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');
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