Wallace Tan wrote: > I am using MySQL v5.0.77 > > MySQL IS using PRIMARY index for the slow query. > > However, after reading comment 19 at: > http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/ > I got the idea to use another index for the COUNT. > > Any MySQL experts can explain this performance gap?
From http://capttofu.livejournal.com/12570.html "InnoDB stores data in primary key order. If you don't specify a primary key, innodb creates one internally. InnoDB uses a clustered index, which means every index is stored with the primary key -- so be careful when making primary keys on InnoDB tables that are long. Clustered indexes give good performance for writes as well as selecting data by index. They are _slow_ with count(*) because: * InnoDB doesn't maintain # rows in the storage engine * Clustered indexes are slow when you perform count(*) because it is a count across the primary key, that operation has to traverse each index and data node. The way to get around this is to use select count(1) from t1; Or select count(<some other indexed column>) from t1;" -- Regards, Wallace M:94500905 _______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
