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)
--
