homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Hardware and OS Related Technologies / Website Technology Issues
Forum Library, Charter, Moderators: phranque

Website Technology Issues Forum

mysql fulltext performance issues
doesn't really work for me

10+ Year Member

Msg#: 3557 posted 8:46 am on Aug 10, 2004 (gmt 0)

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.



WebmasterWorld Senior Member txbakers us a WebmasterWorld Top Contributor of All Time 10+ Year Member

Msg#: 3557 posted 3:32 pm on Aug 22, 2004 (gmt 0)

--- bounce ---


10+ Year Member

Msg#: 3557 posted 3:38 pm on Sep 7, 2004 (gmt 0)

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


10+ Year Member

Msg#: 3557 posted 3:48 pm on Sep 7, 2004 (gmt 0)

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)


10+ Year Member

Msg#: 3557 posted 10:19 pm on Sep 9, 2004 (gmt 0)

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

Global Options:
 top home search open messages active posts  

Home / Forums Index / Hardware and OS Related Technologies / Website Technology Issues
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved