•  

Announcement

Announcement Module
Collapse
No announcement yet.

Using count(*) on a table with a blob

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using count(*) on a table with a blob

    #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?
    Tags: None
  • #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.

    Comment

    • #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?

      Comment

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

        Comment

        • #5
          If you say count(specific_column_name) it will have to read the values and determine that they are non-NULL.

          Comment

          • #6
            Baron, can you confirm that count(*) does not retrieve the rows from the primary index before returning the #?

            Comment

            • #7
              No, but you can! If you see "Using index" in the Extra column of EXPLAIN, then it will not access data outside of the chosen index.

              Comment

              Working...
              X