Michael Monnerie wrote: > The question is: Why is MySQL so stupid not to use the index? That > should be done automatically by the DBMS, that's its job. I would oppose > against changing the query just because MySQL has a bug. Maybe you use a > version that's known to be instable?
I am using MySQL v5.0.77 MySQL IS using PRIMARY index for the slow query. However, after reading comment 19 at: http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/ I got the idea to use another index for the COUNT. Any MySQL experts can explain this performance gap? > SHOW CREATE TABLE dbmail_messageblks\G *************************** 1. row *************************** Table: dbmail_messageblks Create Table: 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=602519 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) > EXPLAIN SELECT COUNT(*) FROM dbmail_messageblks\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: dbmail_messageblks type: index possible_keys: NULL key: PRIMARY key_len: 8 ref: NULL rows: 1930308 Extra: Using index 1 row in set (0.00 sec) > EXPLAIN SELECT COUNT(*) FROM dbmail_messageblks use > index(physmessage_id_index)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: dbmail_messageblks type: index possible_keys: NULL key: physmessage_id_index key_len: 8 ref: NULL rows: 1930310 Extra: Using index 1 row in set (0.00 sec) -- Regards, Wallace _______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
