Peter,
Here are my suggestions based on past and current experience.
1) set-variable = sort_bufffer=8M
Since you have a lot of records there you will no doubt be doing quite
a lot of sorting. Increase the sort_buffer. This would also speed up
new index creation.
2)
set-variable = ft_min_word_len=3 (mysql defaults to 4)
set-variable = ft_max_word_len=100 (mysql defaults to 256)
set-variable = ft_max_word_len_for_sort=10 (mysql defaults to 20 I
think)
In real life, people want to be able to find "Tom" and "Cat" so you
want to fulltext to index 3 letter words as well at the cost of index
size. However, by decreasing the max_word_len and len_for_sort by over
half, you have effectively decreased the full-text index size by 50%.
This would speed up your searches big time.
(must recreate fulltext index for the above changes to take effect)
3)
If you are doing full-text searches on a db that gets a lot of updates
and inserts, do the following.
a) create a slime but duplicate table such as mytable_fulltext
b) mytable_fulltext has only the primary key of mytable and only the
varchar/blob fields that you
need to search on. Create full-text index on them and remove the
full-text index on mytable
c) every night or whatever interval you choose, truncate/empty
mytable_fulltext and do a
insert into mytable_fulltext select key, title, summary from mytable
What the above does it give you constant performance/scalability
during peak load and avoid the table locks of myisam updates and
inserts.
Hope you find it helpful and if you find something that does help and
is even better share it with us since full-text search is one of the
slowest feature of mysql and every ounce of performance boost is worth
the extra effort.
Xing
On Monday, February 17, 2003, at 07:30 PM, Peter Bryant wrote:
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
---------------------------------------------------------------------
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
