Re: 4 Minute Full Text Index searches

[Matthew Caruana Galizia]

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.