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

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

(Imagine the start of a STAR WARS moive with scrolling words...)

A very long ago, I discovered that FULLTEXT indexes will nullify the MySQL Query Optimizer.

• 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

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

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

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

(Imagine the start of a STAR WARS moive with scrolling words...)

A very long ago, I discovered that FULLTEXT indexes will nullify the MySQL Query Optimizer.

• 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

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

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

(Imagine the start of a STAR WARS moive with scrolling words...)

A very long ago, I discovered that FULLTEXT indexes will nullify the MySQL Query Optimizer.

• 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