Mysql LIMIT using MATCH AGAINST

Question

I have this SQL query. If I run this query without the LIMIT, the result of the match it's OK, but when I run this with limit (to make an infinite scroll), i cannot get the best match first, just I get the best match of the LIMIT search, not the first of the entire search separated in 10:

    SELECT videos.id_video as idVideo, 1 as idCanal, MATCH (nombre_prog, programas.descrip_larga) AGAINST ('escuela') as relevancia FROM videos
INNER JOIN programas_videos ON videos.id_video = programas_videos.id_video
INNER JOIN programas ON programas_videos.id_prog = programas.id_prog
WHERE MATCH (nombre_prog, programas.descrip_larga) AGAINST ('escuela' IN BOOLEAN MODE)
ORDER BY relevancia DESC LIMIT 5,5

How can I correct this?

Thank you!

Answer 1

Try this. It will return all of the results of the sub-select first and then apply the limit.

SELECT  *

FROM 

(
SELECT videos.id_video as idVideo, 1 as idCanal, MATCH (nombre_prog, programas.descrip_larga) AGAINST ('escuela') as relevancia FROM videos
INNER JOIN programas_videos ON videos.id_video = programas_videos.id_video
INNER JOIN programas ON programas_videos.id_prog = programas.id_prog
WHERE MATCH (nombre_prog, programas.descrip_larga) AGAINST ('escuela' IN BOOLEAN MODE)
ORDER BY relevancia DESC 
) AS x

LIMIT 5,5