Return to Revisions

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