Using count(*) on a table with a blob

MySQL & MariaDB Other MySQL® Questions
1

I’m using InnoDB on MySQL 5.0. I’m running count(*) query on a table with a blob.

The query looks something like this:

select count(*) from table_name where column_name = ‘value’;

There is an index on column_name, but it’s not the primary key. From what I understand, count(*) is special in that it does not check for non-null values, it just returns the # of rows, but does InnoDB still retrieve the rows from the primary index before counting how many rows there are? Or does it traverse the index on column_name and just count how many rows it would look up and return that? Furthermore, is InnoDB smart enough to optimize count(primary_key_column) where primary_key_column is a not-null field?

2

Or does it traverse the index on column_name and just count how many rows it would look up and return that

this!

Furthermore, is InnoDB smart enough to optimize count(primary_key_column) where primary_key_column is a not-null field?

In InnoDB, the PK is stored at every leaf node, so the rows need not be retrieved. If you use count(non-nullable non-PK column) it will however retrieve the data row for no good reason.

3

Regarding the count(pk), does it need to iterate through the values of pk to determine that they are non-null or does it count the # of rows and return that?

4

I assume it reads the value, but I don’t know enough of MySQL internals to prove this claim. The cost of reading the PK is negligible though.