MySQL Performance: MySQL => should a count with an index take this long?

should a count with an index take this long? [message #1364]

Mon, 04 June 2007 23:56

GeoffreyF67
Messages: 21
Registered: May 2007

Junior Member

CREATE TABLE `WikiParagraphs2` (
  `ID` int(9) unsigned NOT NULL auto_increment,
  `Paragraph` text collate utf8_swedish_ci NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci AUTO_INCREMENT=73035277 ;

mysql> select count(ID) from WikiParagraphs2 where ID < 9999999;
+-----------+
| count(ID) |
+-----------+
|   7375964 | 
+-----------+
1 row in set (44.79 sec)

mysql> explain select count(ID) from WikiParagraphs2 where ID < 9999999;

+----+-------------+-----------------+-------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows    | Extra                    |
+----+-------------+-----------------+-------+---------------+---------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | WikiParagraphs2 | range | PRIMARY       | PRIMARY | 4       | NULL | 7997361 | Using where; Using index | 
+----+-------------+-----------------+-------+---------------+---------+---------+------+---------+--------------------------+
1 row in set (0.05 sec)

It says it's using an index so I don't understand why it would take 45 seconds to get the count...any ideas?

Edit: Added table definition.

[Updated on: Tue, 05 June 2007 00:04]

G-Man

Report message to a moderator