title: Normal select faster than count(*)
body: <p>I want to do a count like this (as an example, not really counting dogs):</p>
  
  <pre><code>SELECT COUNT(*)
  FROM dogs AS d INNER JOIN races AS r ON d.race_id = r.race_id
      LEFT INNER colors AS c ON c.color_id = r.color_id
  WHERE d.deceased = 'N'
  </code></pre>
  
  <p>I have 130,000 dogs in a MyISAM table. Races has 1,500 records and is an InnoDB table with 9 columns, colors has 83 records and is also InnoDB and has two columns (id, name).
  The *_id columns are all primary keys, I have indices on the 'foreign' keys dogs.race_id and races.color_id and I have an index on dogs.deceased. None of the mentioned columns can be <code>NULL</code>.</p>
  
  <pre><code># mysql --version
  mysql  Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2
  </code></pre>
  
  <p>Now the thing is: In my PhpMyAdmin this query takes 1.8 secs (with <code>SQL_NO_CACHE</code>) with a count result of 64,315. Changing <code>COUNT(*)</code> to <code>COUNT(d.dog_id)</code> or <code>COUNT(d.deceased)</code> also takes the query to run for 1.8 secs with the same result.</p>
  
  <p>But when I remove the <code>COUNT()</code> and just do <code>SELECT *</code> or <code>SELECT dog_id</code>, it takes about 0.004 secs to run (and then counting the result with something like <code>mysql_num_rows()</code>).</p>
  
  <p>How can this be? And how can I make the <code>COUNT()</code> work faster?</p>
  
  <p><strong>Edit: Added an <code>EXPLAIN</code> below</strong></p>
  
  <pre><code>EXPLAIN SELECT COUNT(*)
  FROM dogs AS d INNER JOIN races AS r ON d.race_id = r.race_id
      INNER JOIN colors AS c ON c.color_id = r.color_id
  WHERE d.deceased = 'N'
  </code></pre>
  
  <p>Gives me:</p>
  
  <pre><code>+----+-------------+-------+-------+------------------+----------+---------+----------------------+------+-------------+
  | id | select_type | table | type  | possible_keys    | key      | key_len | ref                  | rows | Extra       |
  +----+-------------+-------+-------+------------------+----------+---------+----------------------+------+-------------+
  |  1 | SIMPLE      | c     | index | color_id         | color_id | 4       | NULL                 |   83 | Using index | 
  |  1 | SIMPLE      | r     | ref   | PRIMARY,color_id | color_id | 4       | database.c.color_id  |   14 | Using index | 
  |  1 | SIMPLE      | d     | ref   | race_id,deceased | race_id  | 4       | database.r.race_id   |  123 | Using where | 
  +----+-------------+-------+-------+------------------+----------+---------+----------------------+------+-------------+
  </code></pre>