Andy Henshaw wrote:
Not sure if this is the cause, but from personal experience (lots of rows and big blobs) consider the following:I have the following query that takes anywhere from 1 to 3 seconds to run. I would expect it to run in less than 1/2 a second (and I really need it to do so). I've added the appropriate indices and tried EXPLAIN and ANALYZE; but, I'm not getting anywhere.
SELECT message.id FROM message, message_thread WHERE message.id = message_thread.message_id AND message_thread.thread_id = SOME_CONSTANT_NUMBER
where SOME_CONSTANT_NUMBER is an integer literal, like 16400 (and no, I'm not putting quotes around the number).
Here are the two tables involved:
message table ------------------------------------------------------------------ Field Type NULL Key Default Extra ------------------------------------------------------------------ id int(11) PRI NULL auto_increment msgtype_id int(11) MUL NULL content blob YES precedence varchar(255) YES
Every time you search the message table the BLOB is being loaded into memory even if it is not being returned in the query.
Instead, break apart this table into 2:
message(id, msgtype_id,precedence) message_blob(id, content)
Then select the row you want from message_blob from a list of rows from message.
message_thread table ------------------------------------------------------------------ Field Type NULL Key Default Extra ------------------------------------------------------------------ id int(11) PRI NULL auto_increment message_id int(11) MUL -1 thread_id int(11) MUL -1 contact varchar(255) YES
The message table has 1,117,213 records and the message_thread table has 2,563,893 records. At most, each query will return 200 records.
Here is the output of the explain function (turned sideways):
--------------------------------------------------------------- table : message_thread message --------------------------------------------------------------- type : ref eq_ref --------------------------------------------------------------- possible_keys : thread_id, PRIMARY message_id --------------------------------------------------------------- key : thread_id PRIMARY --------------------------------------------------------------- key_len : 4 4 --------------------------------------------------------------- ref : const message_thread.message_index --------------------------------------------------------------- rows : 200 1 --------------------------------------------------------------- Extra : Using where Using index ---------------------------------------------------------------
This is running on a 2.2 GHz Pentium 4, NTFS File system, MS Windows 2000, MySQL 4.0.18. Each table is an InnoDB type.
I've tried a combined thread_id/message_id index in the message_thread table; but, that did not seem to help at all.
Any help would be appreciated.
HTH,
Robert J Taylor [EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
