How to make a FULLTEXT search with ORDER BY fast?

Question

I'm trying to get a simple FULLTEXT match to be faster when using order by on another column on a table with over 100 million rows. The basis is one table with a fulltext on two columns and I want to search the database but order it by either the primary (least/most recent) or popularity. Is it possible to make a FULLTEXT with an order by on another indexed column fast? SQL Fiddle below with schema and explains of all queries:

See SQL Fiddle #1

What's very fast so far is denormalization of search columns in a separate table and a join but I would rather not have another table if not necessary. SQL Fiddle below (denormalized query at the end):

See SQL Fiddle #2

Answer 1

The first problem here is something you cannot control. What is it ???

The Query Optimizer's reaction to a FULLTEXT index. Why ?

(Now imagine the start of a STAR WARS movie with scrolling words...)

A very long time ago, I discovered that FULLTEXT indexes will nullify the MySQL Query Optimizer's use of other indexes

• Jun 10, 2011 : Mysql fulltext boolean ignore phrase
• Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
• Mar 18, 2012 : Why is LIKE more than 4x faster than MATCH...AGAINST on a FULLTEXT index in MySQL?
• AND MORE LIKE IT !!!

SUGGESTION

Refactor the Query so that the MATCH ...AGAINST collects keys only

EXAMPLE #1

SELECT a.id FROM a 
WHERE
MATCH (`search1`,`search2`) AGAINST ('aaaa' IN BOOLEAN MODE)
ORDER BY a.id DESC
LIMIT 5;

should become something like

SELECT N.id FROM
(SELECT id FROM a WHERE
MATCH (`search1`,`search2`) AGAINST ('aaaa' IN BOOLEAN MODE)) M
INNER JOIN a N USING (id)
ORDER BY N.id DESC LIMIT 5;

EXAMPLE #2

SELECT a.id,a.popularity FROM a 
WHERE
MATCH (`search1`,`search2`) AGAINST ('aaaa' IN BOOLEAN MODE)
ORDER BY a.popularity DESC
LIMIT 5;

should become something like

SELECT N.id,N.popularity FROM
(SELECT id FROM a WHERE
MATCH (`search1`,`search2`) AGAINST ('aaaa' IN BOOLEAN MODE)) M
INNER JOIN a N USING (id)
ORDER BY N.popularity DESC LIMIT 5;

CONCLUSION

The main idea: Collect the keys using MATCH ...AGAINST and join it back to the source table

Answer 2

Got the same problem and found a convenient solution - at least for my tasks.

If you always have the same ORDER BY eg popularity or whatever and don't do frequent updates, e.g. if it's enough on a daily basis, an idea is to set up a cronjob which resorts the table every night.

ALTER TABLE my_table ORDER BY my_sort_column ASC/DESC

This way you get the sorted results without filesort and any ORDER BY clause in the query. For my tasks it took down the query time from about 0.5-0.9 sec to ~0.004 for certain queries. The nightly ALTER query takes about 5-7sec on a 250k table and 11 year old Dell server.

NOTE: Works probably only with MyISAM tables, thank you for the comment @Kevin! (demo).