Get row count from Information Schema with additional column filter in MySQL

Question

I'm trying to get some stats from a MySQL InnoDB table. To get faster results, I'm using:

SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = "botonera" AND TABLE_NAME = "log";

It works fine, but I also need to combine a specific filter within those results:

WHERE log.id_cat = 1

How can I combine INFORMATION_SCHEMA results with a specific column filter inside the table in question? Thanks.

Answer 1

As Barmar noted, the TABLE_ROWS is only an estimate for InnoDB tables. It's not a simple answer what the number of rows is, in a storage engine that supports transaction isolation.

• Is it the number of rows including uncommitted rows?
• Is it the number of rows committed?
• Is it the number of rows visible to your transaction (if you use repeatable-read)? Or visible to another transaction? Or is it all committed rows (as though using read-committed), even though your transaction can't see all of those rows?

So Barmar is right, the INFORMATION_SCHEMA is not the right place to get a filtered count.

This also points out why it takes a long time to do a COUNT(*) query against InnoDB. It has to evaluate every row in the table to see if that row belongs in your transaction's view of the database. This is the downside of using an ACID database.

If you need to access the count with high performance, you'll need to keep track of the count as the result of the aggregate query, and store that value somewhere else, like another table, or an in-memory cache.