Innodb tables do not cache the rowcount like MyISAM tables do, so mysql has to walk the entire table to get a count. If you have a unique index, it should be able to scan that instead, which will be faster.
On Sat, 2006-06-17 at 20:32 +0530, Alex Arul wrote: > 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] > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
