mysql fulltext performance issues

doesn't really work for me

[flex55]

i have a large db, with ~2 million product records, each has product name, product description, reviews, etc (dozens of words per record).
i have created a fulltext index for this table- only for product name and product description to start with, and tried to run full text search on it.

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.

[txbakers]

--- bounce ---

[pete_m]

flex55 - I had a similar problem to this (although with 350,000 records, rather than 2m). It turns out that I'd overlooked part of the mysql documentation.

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 worked like a dream - queries in well under 1 second.

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...

[JasonHamilton]

I have near instant searches using fulltext on a database of about 1 million records. However, if you searched for a string that is common among the records, those will upwards of 4-6 secs to return.

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)

[danieljean]

The mods are going to hate me for this... I know using MySQL for a full-text search is a popular way of doing things, but it's certainly not optimal.

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! :)