how to avoid identical expressions in sql SELECT and WHERE?

Question

Is there a way to avoid writing twice in a query an expression such as the one in my example?

SELECT
    MATCH(test) AGAINST("str" IN BOOLEAN MODE) AS testRelevance,
    .......
FROM
    mytable
WHERE
    .......
    MATCH(test) AGAINST("str" IN BOOLEAN MODE) > 0

i have tried already

SELECT
    MATCH(test) AGAINST("str" IN BOOLEAN MODE) AS testRelevance,
    .......
FROM
    mytable
WHERE
    .......
HAVING
    testRelevance > 0

but that's not good for big tables (only in some cases apparently?)

thank you in advance

possible duplicate of [Using an Alias in a WHERE clause](http://stackoverflow.com/questions/356675/using-an-alias-in-a-where-clause) — Alexander, Mar 02 '14 at 09:19

score 1 · Accepted Answer · answered Mar 02 '14 at 09:19

1

Use a derived table:

select *
from (
   select MATCH(test) AGAINST("str" IN BOOLEAN MODE) AS testRelevance,
          ....
   from ...
   where ... 
) as t
where testRelevance > 0

It should be as fast as the version without the derived table, but given MySQLs limited query optimizer you should probably check the execution plan to see if MySQL indeed can push the condition effectively into the derived table and make use of indexes.

answered Mar 02 '14 at 09:19

thank you, i'm going to check if mysql can use indexes in a derived table. thanks! – Mar 02 '14 at 09:25
turns out that you can't do that, i'm getting tired of mysql – Mar 03 '14 at 16:25
@Wes: I never understood how MySQL could ever get so popular missing so many features that should be self-evident in a modern DBMS – Mar 03 '14 at 17:11
people are afraid of change :( – Mar 03 '14 at 18:58

how to avoid identical expressions in sql SELECT and WHERE?

1 Answers1