How to make a FULLTEXT search with ORDER BY fast? 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
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 &#39;number of words&#39; 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