MySQL Forums
Forum List  »  Full-Text Search

Re: 4 Minute Full Text Index searches
Posted by: Matthew Caruana Galizia
Date: November 22, 2007 06:47PM

I don't think this is the root of the problem - but SELECT * means that you are potentially returning half a mb of data for just 7 results (the text type can contain ~60kb).

What I did to cut query times was to copy the data that I wanted to search onto a separate table, reducing the number of columns on the indexed table. In your case, you could create a table with just two columns: the id and the title. If you want to show a description with the results, you can add a third column, and use SELECT LEFT(description,30) when copying the data. If you don't want words to be chopped off midway, use SELECT SUBSTRING_INDEX(description,' ',30) to copy the first 30 words.

PS: Someone please correct me if I am wrong, because I am really not sure about this, but I think that LIKE can be faster than MATCH because LIKE can only return one of two values (true or false) whereas MATCH keeps going until after the first hit to return a relevance value. So if you don't want your results sorted by relevance, just use LIKE.

Edited 1 time(s). Last edit at 11/22/2007 06:54PM by Matthew Caruana Galizia.

Options: ReplyQuote

Written By
Re: 4 Minute Full Text Index searches
November 22, 2007 06:47PM

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.