3

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

3

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.