Peter Rabbitson wrote: > 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) >
Forgot to mention: mysql> select count(messageblk_idnr) from dbmail_messageblks; +------------------------+ | count(messageblk_idnr) | +------------------------+ | 1752792 | +------------------------+ mysql> SHOW VARIABLES LIKE "%version%"; +-------------------------+------------------+ | Variable_name | Value | +-------------------------+------------------+ | protocol_version | 10 | | version | 5.0.51a-15-log | | version_comment | (Debian) | | version_compile_machine | i486 | | version_compile_os | debian-linux-gnu | +-------------------------+------------------+ _______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
