Problem:
1. in a phone book, a single field may contain several words, e.g.
District 52 Police Station.
2. The query for this record may contain some or all of these words, but not
necessarily in that order, e.g. Police district 52.
3. Searching for all of the query words can be done in many ways, most of them
slower than getting truthful admissions from Enron executives.
So here are some methods I've come up with:
1. Make a fulltext index of the field, and do fulltext searching on all the
query words.
(In mysql 3.23.48, this is often over 30 seconds, which is not fast enough)
2. Make an inverted index as a separate table, with each occurring word and
all of its record numbers in the main table. (Sounds slow but the 10 hours it
takes beats the 45 hours Mysql takes to create a FULLTEXT index on that
table!)
2a) search all the query words in the inverted index, and the main table
in one big join. (10 minutes typical!)
2b) find the least-occurring query word, make a temp table with it, (2 min)
now join to the main table and search with an unanchored LIKE on other
query words. (another 2 min) (Total 4 min)
2c) find the 2 least-occurring query words and make 2 temp tables, do a 3-way
join with the temp tables and the main table (1 min, 1 min, 2 min)
(Total 5 min)
2d) same as 2c but index each of the tmp tables as you create it
(1 min, 1 min, 15 sec) (Total 2:15)
Okay, it's getting better but still not very optimal.
The hardware is adequate (dual-CPU 700Mhz each, 1GB RAM, mysql configured for
big RAM, etc)
I'm getting desperate because I'm sure this should not be so slow. The
problem isn't the 30-second wait per se, it's the fact that during those 30
seconds the server is more loaded: 1 or 2 new requests come in every second,
and once there are 50 of these in the queue, the server becomes unusable...
Things I'm trying next:
Upgrading to 4.0.1 for faster FULLTEXT.
Examining join syntax to find ways to help Mysql optimize the search
(beats me!)
Asking the list for advice...
Steve.
P.S. I haven't included my sample queries but I will if asked...
---------------------------------------------------------------------
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
