*************************************************************
I left out a piece of probably important information the first time:
search_messages_archive contains 11.5 million records.
messages_archive contains 760,000 records.
Thanks.
-karl
*************************************************************
I've been having a problem for quite awhile now with a particular query
sometimes taking a long time. The query in question is a web-based
search for messages in a bbs archive. Every time a message is added to
the bbs each word in the message is parsed out and added to the
'search_messages_archive' table. It is based on the 'Slapping together
a search engine...' article on phpbuilder.com.
When the query hangs the server load spikes and can cause other queries
to fail.
The tables involved in the query are:
mysql> show columns from search_messages_archive;
+----------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra
|
+----------------+-----------------------+------+-----+---------+-------+
| word | char(50) | | MUL | |
|
| message_number | mediumint(8) unsigned | | MUL | 0 |
|
+----------------+-----------------------+------+-----+---------+-------+
mysql> show columns from messages_archive;
+---------------------+-----------------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default
| Extra |
+---------------------+-----------------------+------+-----+------------+-------+
| message_number | mediumint(8) unsigned | | MUL | 0
| |
| message_area_number | smallint(5) unsigned | | MUL | 0
| |
| message_area | varchar(100) | | |
| |
| topic_number | mediumint(8) unsigned | | MUL | 0
| |
| topic | varchar(100) | | |
| |
| message | text | | |
| |
| post_date | date | | | 0000-00-00
| |
| post_time | time | | | 00:00:00
| |
| user_name | varchar(40) | | MUL |
| |
+---------------------+-----------------------+------+-----+------------+-------+
14 rows in set (0.00
sec)
Here is the query 'EXPLAINED':
EXPLAIN SELECT DISTINCT messages_archive.message_number FROM
search_messages_archive,messages_archive
WHERE messages_archive.message_number =
search_messages_archive.message_number
AND search_messages_archive.word IN ('warmoth')
LIMIT 0,11;
+-------------------------+-------+---------------------+----------------+---------+----------------------------------------+------+-----------------------------+
| table | type | possible_keys | key
| key_len | ref
| rows | Extra |
+-------------------------+-------+---------------------+----------------+---------+----------------------------------------+------+-----------------------------+
| search_messages_archive | range | word,message_number | word
| 50 | NULL
| 1630 | where used; Using temporary |
| messages_archive | ref | message_number | message_number
| 3 | search_messages_archive.message_number | 19 | Using
index |
+-------------------------+-------+---------------------+----------------+---------+----------------------------------------+------+-----------------------------+
+
Now here are the results of the query:
>From the slow query log (26 seconds):
# Time: 010215 11:24:06
# User@Host: [nobody] @ localhost []
# Time: 26 Lock_time: 0 Rows_sent: 11
SELECT DISTINCT messages_archive.message_number FROM
search_messages_archive,messages_archive
WHERE messages_archive.message_number =
search_messages_archive.message_number
AND search_messages_archive.word IN ('warmoth')
LIMIT 0,11;
The same query run from the command line: (only .39 seconds!)
mysql> SELECT DISTINCT messages_archive.message_number FROM
search_messages_archive,messages_archive WHERE
messages_archive.message_number = search_messages_archive.message_number
AND search_messages_archive.word IN ('warmoth')
LIMIT 0,11;
+----------------+
| message_number |
+----------------+
| 427 |
| 438 |
| 622 |
| 1485 |
| 2147 |
| 1520 |
| 1675 |
| 679 |
| 1021 |
| 1226 |
| 2560 |
+----------------+
11 rows in set (0.39 sec)
Even now as I run the query from the web browser again it is very fast.
I just have not been able to track down what causes this query to be so
slow at times and very fast at others.
MySQL version: 3.23.32
OS: SuSE Linux (2.2.10 kernel)
System: PIII w/ 256Mb RAM
Let me know if more info is required
TIA.
-karl
---------------------------------------------------------------------
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
