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 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]