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

StackExchange Database Administrators Q#145490, answer score: 4