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?