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.

Answer 2

There's no point in doing this with INFORMATION_SCHEMA. If you need to test data in the table, you have to query the table itself:

SELECT COUNT(*)
FROM log
WHERE id_cat = 1

INFORMATION_SCHEMA just contains static information about the tables, it doesn't have any information about the table contents.

If you have an index on id_cat, this query will be efficient.