CODEWITHSUNDEEP
mysqlsql
user652649
user652649

Reputation:

how to avoid identical expressions in sql SELECT and WHERE?

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

Upvotes: 4

Views: 82

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 1

Related Questions