# Using count(\*) on a table with a blob

**URL:** <https://forums.percona.com/t/using-count-on-a-table-with-a-blob/1343>\
**Category:** Other MySQL® Questions\
**Created:** [February 5, 2010, 2:10am UTC](https://forums.percona.com/t/using-count-on-a-table-with-a-blob/1343 "2010-02-05T02:10:56Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![arya](https://avatars.discourse-cdn.com/v4/letter/a/a4c791/32.png) [@arya](https://forums.percona.com/u/arya)\
**Post date:** [February 5, 2010, 2:10am UTC](https://forums.percona.com/t/using-count-on-a-table-with-a-blob/1343/1 "2010-02-05T02:10:56Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [February 5, 2010, 2:15pm UTC](https://forums.percona.com/t/using-count-on-a-table-with-a-blob/1343/2 "2010-02-05T14:15:48Z")

</div>

> > 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.

---

<div class="post-metadata">

**Author:** ![arya](https://avatars.discourse-cdn.com/v4/letter/a/a4c791/32.png) [@arya](https://forums.percona.com/u/arya)\
**Post date:** [February 5, 2010, 2:20pm UTC](https://forums.percona.com/t/using-count-on-a-table-with-a-blob/1343/3 "2010-02-05T14:20:27Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [February 5, 2010, 2:29pm UTC](https://forums.percona.com/t/using-count-on-a-table-with-a-blob/1343/4 "2010-02-05T14:29:32Z")

</div>

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.