MySQL Forums
Forum List  »  Optimizer & Parser

Re: Stop using filesort
Posted by: Jørgen Løland
Date: May 10, 2011 12:54PM

> Thanks Jorgen. You are a great help. I wouldnt pretend to know something MySQL knows. I hope I didnt come across that way. :-)

Glad I could help. And don't worry, you didn't...

> Just to be sure, I can set up the FULLTEXT INDEX, but to make sure it uses the topic_id to resolve ordering I use the FORCE INDEX topic_id_idx statement? Stopping filesort.

When you're done creating the table and filling it with data, you can run your query with EXPLAIN. That will tell you which index is used, and most likely it will be the fulltext index since that speeds up the fulltext search significantly. If you still prefer getting ordering for free instead of a cheap fulltext search, you can use FORCE INDEX to make MySQL do as you want. Normally, using FORCE INDEX is not a good idea, but in some cases it is.

To put it another way: lets say you have 1000 rows and a search term that matches 10 of these rows. Using the fulltext index, MySQL will look up only these 10 rows through the index and then perform filesort on them (and sorting 10 rows isn't bad at all). Alternatively, MySQL can use the other index to avoid filesort, but then it has to do inefficient search term matching on all 1000 rows instead.

> Also, what does boolean mode do to the score?

With boolean mode you'll get 0 for no search term hit, 1 for at least one search term hit. Thus, you won't be able to distinguish two rows that both match the search term even though one is a closer match than the other. Without boolean mode, you'll get a number better capable of indicating how close the hit is. Boolean mode is probably good in the WHERE clause but not for sorting. Try this to get an idea:

SELECT title, content, MATCH(title,content) AGAINST ('searchterm' IN BOOLEAN MODE) AS bool_score, MATCH(title,content) AGAINST ('searchterm') AS nonbool_score FROM search;

Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle

Options: ReplyQuote

Written By
May 06, 2011 02:14PM
May 08, 2011 01:28PM
May 09, 2011 10:57AM
May 10, 2011 04:40AM
May 10, 2011 11:41AM
Re: Stop using filesort
May 10, 2011 12:54PM
May 12, 2011 08:04AM
May 12, 2011 08:52AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.