slow count(*) on innoDB
I have a table message_message with 3000000 records.
when I make a count(*) query, It's very slow...:
mysql> select count(*) from message_message ;
+----------+
| count(*) |
+----------+
| 2819416 |
+----------+
1 row in set (2 min 35.35 sec)
explain it: mysql> explain select count(*) from message_message ;
| id | select_type| table | type | possible_keys | key | key_len | ref | rows |Extra |
| 1 | SIMPLE | message_message | index | NULL | PRIMARY | 4 | NULL | 2939870 | Using index |
1 row in set (0.02 sec)
what happen?
Have a look at This Post in InnoDB you need to do a full table scan, where as in MyISAM its a index read.
If you use a where clause though it changes the execution pattern to use indexes, so in general InnoDB will be slower than MyISAM on full unrestricted counts, where as the performance matches up on restricted counts.
If you want to count the amount of records, it's better to query the whole table and use the num_rows property of the result set. Count(...) is usually used when you want to have aggregate queries (in combination with GROUP BY ).
上一篇: 我如何优化我的MySQL查询？
下一篇: innoDB上的计数较慢（*）