count(*) is slow in innodb due to Multi Versioning. Which table type are you using ?
Thanx Alex On 6/9/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
Hi everybody, MySQL 5.0.21 running on RedHat EL4, 2GHz CPU, 2,5GB RAM, RAID5/128MB RAM. At one point I had to issue the following query on a 1.8GB 42mil records table: SELECT COUNT(*) FROM geoRecord WHERE geoRecord.geolatitude IS NOT NULL ; As it took forever to complete (I mean more than 20 minutes) I've stopped the client and attempted to work around. The table looks like this: EXPLAIN geoRecord; +-----------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-------------+------+-----+---------+----------------+ | georecordkey | int(11) | NO | PRI | NULL | auto_increment | | geobiorecordkey | int(11) | NO | MUL | 0 | | | geolocationtext | varchar(85) | YES | MUL | NULL | | | geoprecision | int(11) | YES | MUL | NULL | | | geolatitude | float | YES | MUL | NULL | | | geolongitude | float | YES | MUL | NULL | | +-----------------+-------------+------+-----+---------+----------------+ The indexes on it look like this: SHOW INDEX FROM geoRecord ; +-----------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-----------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ | geoRecord | 0 | PRIMARY | 1 | georecordkey | A | 47808182 | NULL | NULL | | BTREE | | | geoRecord | 1 | geobiorecordkey | 1 | geobiorecordkey | A | 47808182 | NULL | NULL | | BTREE | | | geoRecord | 1 | geolocationtext | 1 | geolocationtext | A | 11952045 | NULL | NULL | YES | BTREE | | | geoRecord | 1 | geoprecision | 1 | geoprecision | A | 23904091 | NULL | NULL | YES | BTREE | | | geoRecord | 1 | geolatitude | 1 | geolatitude | A | 5976022 | NULL | NULL | YES | BTREE | | | geoRecord | 1 | geolongitude | 1 | geolongitude | A | 5976022 | NULL | NULL | YES | BTREE | | +-----------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ Also: EXPLAIN SELECT COUNT(*) FROM geoRecord WHERE geoRecord.geolatitude IS NOT NULL ; +----+-------------+-----------+-------+---------------+-------------+---------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+-------------+---------+------+----------+--------------------------+ | 1 | SIMPLE | geoRecord | range | geolatitude | geolatitude | 5 | NULL | 44891097 | Using where; Using index | +----+-------------+-----------+-------+---------------+-------------+---------+------+----------+--------------------------+ I've run a CHECK TABLE on it, it came OK too. I've run "ANALYZE TABLE geoRecord " already. Attempting to solve the problem I've come up with this: SELECT COUNT(georecordkey) FROM geoRecord WHERE geoRecord.geolatitude IS NOT NULL ; This query takes 1 min and 20 seconds and gives the right answer (still way too long IMO) but then why on earth is the COUNT(*) behaving so bad? If there's a mistake it's got to be so obvious that it eludes me. :-| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
