Bligoo's Tech Blog

MyISAM, an aged MySQL engine with no transactional support and awful scalabililty spoiled many developers with an ultra fast count(*) implementation. But is ultra fast only when there is no conditions in the where part of the sentence.

At least in InnoDB (the common replacement for MySQL, at least until Maria gets more traction) every count(*) has to make a temporary table with all the matching lines and the count the lenght of the table.

There are two ways to deal with count(*).

1.- Don't use count(*) rather count(primaryKey).

     In InnoDB the primary key is always part of any index so, if the condition is decent and you have an index for it the temporaty table willl be in the index ergo much much faster.

2.- Just dont use count(*).

     In many cases the use of count is just lazy thinking. For example developers use the count just to see if its non-zero

     $count=do_sql("select count(*) from table where condition");

     if($count > 0){

          do_something();

     }

     That can be replaced with a much faster:

     $item=do_sql("select primaryKey from table where condition limit 1");

      if($item is not null){

          do_something();

      }

    Is so much faster because when you limit the result mysql creates the same result table but stops right after it has 1 item.