Wednesday, March 7, 2012

SELECT COUNT(*) and WHEN

I observed this very interesting MySQL issue. Basically, I have a huge tables with millions of rows in it. It has an ID column which is an auto-incremented integer primary key.

It took me 20 seconds to do SELECT COUNT(*) FROM tbl yet it only took me 4 seconds to do SELECT COUNT(*) FROM tbl WHEN id > 0.

Basically, these two queries return the same value. One would have expected the later to run a bit slower but the experiment proved otherwise. It seems quite counterintuitive. Why?

3 comments:

  1. what happens when you use EXPLAIN EXTENDED on both queries? maybe the id > 0 causes it to use an index

    ReplyDelete
  2. Hi Steve,

    In both cases, "using index" is marked in the "Extra" field.

    But, the point of this post is that MySQL just doesn't do what you would think it would do. There's still some magical dust in it.

    Thanks for visiting.

    ReplyDelete
  3. Hi, I met the same problem when I was handling with millions of records in MySQL InnoDB. The primary key id is indexed in BTREE. So if you COUNT id>?, only part of records are visited.

    If I use MyISAM engine, it always uses 0ms to COUNT a table because it stores the number of total records.

    ReplyDelete