Re: COUNT(*) very slowly in large InnoDB table

[Dominik Leiner]

@Rick James: Give me a try if I got your explanations right :)

If I got him right, COUNT(*) will look for the smallest available index in the InnoDb table. If there is only the ID (as primary key index), a COUNT(id) will be performed. Therefore no significant difference in performance shall be observed between COUNT(*) and COUNT(id).

If there is another index for that table, a COUNT(theOtherIndex) may be faster, because secondary indices are stored out of the main data structure. Therefore less data must be scanned and there is an increase in performance for the SELECT operation.

Finally you may consider using a SHOW TABLE STATUS to get a rough approximation (up to 50% error) of the table's length. In my case this approximation is well enough in 9 of 10 cases.

Dominik