Is there a better query to get most matched result

72 Views Asked by At

I'm trying to get most matched result using MATCH AGAINST.
My first query works correctly, but it using filesort.
And the second query also works well, but the query is duplicated and looks messy.

How can I optimize my query?
For reference, the index columns are class_id and name.

*query with match against only

    SELECT id, name, MATCH (name) AGAINST('john smi') AS score
    FROM member
    WHERE class_id=5
    AND MATCH (name) AGAINST('john smi');

enter image description here What I want is two top values ​​with the highest score.

*my first query

    SELECT * FROM (
        SELECT id, name, MATCH (name) AGAINST('john smi') AS score, dense_rank() over (order by  MATCH (name) AGAINST('john smi') desc) ranking
        FROM member
        WHERE class_id=5
        AND MATCH (name) AGAINST('john smi')
    )t where t.ranking = 1;

*second query

    SELECT id, name, MATCH (name) AGAINST('john smi') AS score
    FROM member
    WHERE class_id=5
    AND MATCH (name) AGAINST('john smi') AND MATCH (name) AGAINST('john smi') >= (
        SELECT MATCH (name) AGAINST('john smi')
        FROM member
        WHERE class_id=5
        AND MATCH (name) AGAINST('john smi') limit 1
    )l
0

There are 0 best solutions below