Sergey Spiridonov <[EMAIL PROTECTED]> said: > Sergey Spiridonov wrote: >> problem is in query: SELECT messageblk FROM dbmail_messageblks LEFT JOIN >> dbmail_messages USING (physmessage_id) WHERE >> dbmail_messages.message_idnr = '551047' ORDER BY messageblk_idnr >> >> which takes up to 6 seconds!!! > > Slightly rewritten query > > SELECT messageblk FROM dbmail_messageblks, dbmail_messages WHERE > dbmail_messageblks.physmessage_id = dbmail_messages.physmessage_id AND > dbmail_messages.message_idnr = '551047' ORDER BY messageblk_idnr; > > is executed quickly. But I'm not sure if it will do exactly the same.
I get the same results for both queries, however I also get the same EXPLAIN. I'm using MyISAM tables with the earlier 2.0 defs renamed to dbmail_ by hand. Which database scripts did you use? I can run them on a test database on my machine and see if there are any differences in the indices. Aaron mysql> explain SELECT messageblk FROM dbmail_messageblks, dbmail_messages WHERE dbmail_messageblks.physmessage_id = dbmail_messages.physmessage_id AND dbmail_messages.message_idnr = '1' ORDER BY messageblk_idnr\G mysql> explain SELECT messageblk FROM dbmail_messageblks LEFT JOIN dbmail_messages USING (physmessage_id) WHERE dbmail_messages.message_idnr = '1' ORDER BY messageblk_idnr\G *************************** 1. row *************************** table: dbmail_messages type: const possible_keys: PRIMARY,physmessage_id key: PRIMARY key_len: 8 ref: const rows: 1 Extra: Using filesort *************************** 2. row *************************** table: dbmail_messageblks type: ref possible_keys: physmsg_index key: physmsg_index key_len: 8 ref: const rows: 2 Extra: Using where 2 rows in set (0.00 sec) --