I'm converting a table to innodb from myisam in mysql 4.0 and I was wondering why it takes sooo long to do a SELECT COUNT(*)
In the old MyISAM table, it's quick: mysql> select count(*) from forecast; +-----------+ | count(*) | +-----------+ | 194698187 | +-----------+ 1 row in set (0.00 sec) In the Innodb table, which has far less rows (it is still in the process of being loaded) it's much slower: mysql> select count(*) from forecast_avn; +----------+ | count(*) | +----------+ | 4815579 | +----------+ 1 row in set (2 min 12.63 sec) running EXPLAIN on the innodb table gives me this: mysql> explain select count(*) from forecast_avn; +--------------+-------+---------------+---------+---------+------+--------- +-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------------+-------+---------------+---------+---------+------+--------- +-------------+ | forecast_avn | index | NULL | PRIMARY | 4 | NULL | 1789719 | Using index | +--------------+-------+---------------+---------+---------+------+--------- +-------------+ 1 row in set (0.00 sec) wherease the myisam table gives me this: mysql> explain select count(*) from forecast; +------------------------------+ | Comment | +------------------------------+ | Select tables optimized away | +------------------------------+ 1 row in set (0.02 sec) So it looks like myisam's got some optimizations that innodb doesn't have. What's weird, though, is that the row count on the index in the EXPLAIN query is way off...I could run 'myisamchk --analyze' on the myisam tables...are there any optimization tools for innodb available? If you noticed the result from the first query above, you'll see that recreating the table periodically is not much of an option. Thanks Partap Davis Syncrasy, LLC --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
