Welcome to WebmasterWorld Guest from 18.104.22.168
Forum Moderators: phranque
of course, i optimized the my.cnf file to include a bigger parameters:
set-variable = key_buffer_size=400M
set-variable = query_cache_size=30M
set-variable = query_cache_limit=30M
but- simple text search (match against) of 1 word will work fine (a few ms), 2 words text search will take a few seconds, and 3 words and up will take minutes- and minutes is just too much for a website.
does anyone have experience with optimizing this fulltext query (even though, i think, it's pretty straight forward). also- how come it's so slow? i would expect mysql to be much faster on this.
I had a single full-text index on five columns (A,B,C,D,E).
However, my match SQL looked like this:
SELECT *, MATCH (A,B,C) AGAINST ('whatever') FROM my_table
I couldn't work out why it was taking 5-10 seconds to query, when mysql full-text was supposed to be so fast.
The reason was that the MATCH() columns need to be *identical* to the columns included in the full-text index. Once I changed the code to:
SELECT *, MATCH (A,B,C,D,E) AGAINST ('whatever') FROM my_table
It's taken me *months* to figure this out! Of course, this might not be the problem you're having, but it's worth taking a look as a sanity check...
I've not had good luck with 3+ million records and full text, as common searches can take 20 secs to return.
and, you have to look up the mysql docs or the mysql news groups to see how to properly form your fulltext queries. You can issue a fulltext query as you would any normal query, but with a large database it will take forever. The proper syntax repeats the search twice, strange, but it works (I found this from the mysql news groups which a mysql developer was answering about fulltext)
Assuming users will make spelling mistakes, "real" search engines like Lucene might better fit the bill. You can also avoid some of the load on the db server, which makes it easier to scale.
Either way, good luck! :)