StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PONormal select faster than count(*)
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload