Slow Fulltext Search on Large Table

[Peter Bryant]

My problem is that my fulltext searches run quite slowly.

I have a 2300MB table with 4.3M rows and an index (primarily a fulltext index) of 1300M. My hardware is a dual Athlon MP1500+ with 2GB of memory. I am running MySQL 4.0.10.

My my.cnf includes:
set-variable = key_buffer=16M
set-variable = key_buffer_size=128M
set-variable = max_allowed_packet=16M
set-variable = sort_buffer=4M
set-variable = max_connections=1024
set-variable = max_connect_errors=9000000
set-variable = table_cache=1200

I have found that:
1) Searching for non-existant words is fast (<1s)
2) Searching for common words is slow (e.g. select a count of matches on a single word with 15135 matches on a 2300MB table takes 3 min 38.54 sec)
3) Boolean search for a +common word +nonexistant word completes immediately.
4) Add in a LIMIT n on a popular term and the results complete quicker. Completion time is less than O(n). O(log n?)
5) A repeat search runs faster (<1s vs. 35s). Presumably because of OS file caching and the key_buffer_size variable.
6) "against ('aword') limit 2000" and "against ('+adifferentword' in boolean mode) limit 2000" take about the same time to reach their limits (2000).
7) "match(subject, message_body) against ('Linux Kernel') and message_body like '%Linux%' and message_body like '%kernel%' limit 2000" returns in about the same time as "against ('+Linux +kernel' in boolean mode) limit 2000" (24s)

On smaller tables search runs 'acceptably fast'. However, with this larger table, performance on certain searches is unacceptable. I'm having to resort to killing database connections when they run too long.

So, onto my questions:
1) Are there any server settings I should be looking at to improve fulltext search performance?
2) Can my SQL be written differently (e.g. is boolean mode faster or not?)
3) Is there a way to get a list of the most commonly occuring words in a fulltext index? Then I can avoid them in searches. MySQL doesn't happen to drive it's queries by finding the word with lowest frequency first does it?
4) Are there any MySQL code changes on the horizon that could help to speed up fulltext word searching?
5) Is there anything else I could do that would allow me to get results faster (within about a 30 second timeframe). Having searches that don't return all matching rows would be acceptable (but not desirable). e.g. I'm thinking of just deleting less important rows from my large table.

Thanks in advance for any help,

Peter Bryant


_________________________________________________________________
Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail


---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php