Get random rows if full text result is less than N rows

Question

I am using below code to find something

SELECT * FROM table 
    WHERE MATCH(title) AGAINST('php tutorial') LIMIT 25

If there are titles that match my query, It returns 25 rows. If the row is empty or if less than 25 rows I want to get random 25 rows from table. Is it possible in a single query?

Answer 1

Instead, use order by and limit:

SELECT *
FROM table
ORDER BY MATCH(title) AGAINST('php tutorial') DESC
LIMIT 25

Note that this could be much more expensive, depending on the size of your data because the entire table needs to be sorted.

A more efficient method would be:

select t.*
from ((select t.*, MATCH(title) AGAINST('php tutorial') as score
       from table t
       where MATCH(title) AGAINST('php tutorial')
       limit 25
      ) union all
      (select g.*, -1 as score
       from table g
       where NOT (MATCH(title) AGAINST('php tutorial'))
       limit 25
      )
     ) t
order by score desc
limit 25;

This limits the overall sorting to at most 50 records. Like your original query, it does not guarantee the highest scoring titles will be returned.