List:General Discussion« Previous MessageNext Message »
From:Karl Sloth Date:February 16 2001 12:43am
Subject:sometimes hanging query - more info
View as plain text  
*************************************************************
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
Thread
sometimes hanging query - more infoKarl Sloth16 Feb