MySQL Forums
Forum List  »  Performance

Re: COUNT(*) very slowly in large InnoDB table
Posted by: Rick James
Date: July 21, 2010 12:11PM

http://en.wikipedia.org/wiki/ACID
http://www.tutorialspoint.com/mysql/mysql-transactions.htm
http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-transactions.html

MyISAM (not InnoDB) optimizes
SELECT COUNT(*) FROM tbl;

But virtually every use (both MyISAM and InnoDB) of COUNT() must scan through the data and/or index to derive the answer.

SELECT COUNT(*) FROM tbl WHERE x > 3;
will scan through an index on x if there is one. If not, it will scan through the entire table.

SELECT COUNT(...) FROM t1 JOIN t2 ...;
probably has to at least pretend to build the entire resultset.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.