How to avoid identical expressions in sql SELECT and WHERE?

Question

Is there a way to avoid double-writing in an expression query, such as the example 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 already tried

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

but this is bad for large tables (only in some cases, apparently?)

early

Answer 1

Use the view:

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 a version without a view, but given the limited query optimizer of MySQL, you should probably check the execution plan to make sure that MySQL can actually use this condition in the view and use indexes.