Giulio Ferro wrote:
> Paul J Stevens wrote:
>> It's not the number of rows in messageblks that is your problem, but the
>> rediculous rowscan in the explain output. Could you run that 'explain
>> select' again?
>>
>
> Now, this is interesting:
> -------------------------------------------------------------------------------------------------------------------
>
> explain select messageblk, is_header from dbmail_messageblks where
> physmessage_id=364483 order by messageblk_idnr;
> +----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+---------+-------------+
>
> | id | select_type | table | type |
> possible_keys | key | key_len
> | ref | rows | Extra |
> +----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+---------+-------------+
>
> | 1 | SIMPLE | dbmail_messageblks | index |
> physmessage_id_index,physmessage_id_is_header_index | PRIMARY | 8
> | NULL | 1605498 | Using where |
> +----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+---------+-------------+
>
> 1 row in set (0.00 sec)
> -------------------------------------------------------------------------------------------------------------------
>
>
>
> but if I now run:
> -------------------------------------------------------------------------------------------------------------------
>
> explain select messageblk, is_header from dbmail_messageblks where
> physmessage_id=364483;+----+-------------+--------------------+------+-----------------------------------------------------+--------------------------------+---------+-------+------+-------+
>
> | id | select_type | table | type |
> possible_keys |
> key | key_len | ref | rows | Extra |
> +----+-------------+--------------------+------+-----------------------------------------------------+--------------------------------+---------+-------+------+-------+
>
> | 1 | SIMPLE | dbmail_messageblks | ref |
> physmessage_id_index,physmessage_id_is_header_index |
> physmessage_id_is_header_index | 8 | const | 1 | |
> +----+-------------+--------------------+------+-----------------------------------------------------+--------------------------------+---------+-------+------+-------+
>
> 1 row in set (0.00 sec)
> -------------------------------------------------------------------------------------------------------------------
>
>
> May the problem be in how the db server treats the "order by" clause?
>
> I'm currently running mysql 5.1.28. I may try to downgrade to 5.0.x, but
> I'm
> loath to do that. Maybe I should contact the mysql guys to raise a bug
> or something?
It might be 5.1. Here are my 2 cents:
mysql> select count(*) from dbmail_messageblks where messageblk_idnr = 500;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.42 sec)
mysql> explain SELECT messageblk, is_header FROM dbmail_messageblks
WHERE physmessage_id = 500;
+----+-------------+--------------------+------+-----------------------------------------------------+----------------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys
| key | key_len | ref |
rows | Extra |
+----+-------------+--------------------+------+-----------------------------------------------------+----------------------+---------+-------+------+-------+
| 1 | SIMPLE | dbmail_messageblks | ref |
physmessage_id_index,physmessage_id_is_header_index |
physmessage_id_index | 8 | const | 2 | |
+----+-------------+--------------------+------+-----------------------------------------------------+----------------------+---------+-------+------+-------+
1 row in set (0.73 sec)
mysql> explain SELECT messageblk, is_header FROM dbmail_messageblks
WHERE physmessage_id = 500 ORDER BY messageblk_idnr;
+----+-------------+--------------------+------+-----------------------------------------------------+----------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref |
rows | Extra |
+----+-------------+--------------------+------+-----------------------------------------------------+----------------------+---------+-------+------+-------------+
| 1 | SIMPLE | dbmail_messageblks | ref |
physmessage_id_index,physmessage_id_is_header_index |
physmessage_id_index | 8 | const | 2 | Using where |
+----+-------------+--------------------+------+-----------------------------------------------------+----------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> show create table dbmail_messageblks;
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dbmail_messageblks | CREATE TABLE `dbmail_messageblks` (
`messageblk_idnr` bigint(21) NOT NULL auto_increment,
`physmessage_id` bigint(21) NOT NULL default '0',
`messageblk` longblob NOT NULL,
`blocksize` bigint(21) NOT NULL default '0',
`is_header` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`messageblk_idnr`),
KEY `physmessage_id_index` (`physmessage_id`),
KEY `physmessage_id_is_header_index` (`physmessage_id`,`is_header`),
CONSTRAINT `dbmail_messageblks_ibfk_1` FOREIGN KEY (`physmessage_id`)
REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2913307 DEFAULT CHARSET=utf8 |
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
