Normal selection is faster than counter (*)

Question

I want to do such a count (for example, not counting dogs):

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' 

I have 130,000 dogs in the MyISAM table. Racing has 1,500 entries and is an InnoDB table with 9 columns, colors have 83 entries, and InnoDB and have two columns (id, name). The column * _id is all the primary keys, I have indexes on the "foreign" keys dogs.race_id and races.color_id, and I have an index on dogs.deceased. None of the columns mentioned can be NULL .

 # mysql --version mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 

Now the thing is this: in my PhpMyAdmin this query takes 1.8 s (with SQL_NO_CACHE ) with a count of SQL_NO_CACHE Changing COUNT(*) to COUNT(d.dog_id) or COUNT(d.deceased) also requires that the query be executed for 1.8 s with the same result.

But when I delete COUNT() and just do SELECT * or SELECT dog_id , it takes about 0.004 seconds to run (and then counting the result with something like mysql_num_rows() ).

How can it be? And how can I get COUNT() to work faster?

Edit: added EXPLAIN below

 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' 

Gives me:

 +----+-------------+-------+-------+------------------+----------+---------+----------------------+------+-------------+ | 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 | +----+-------------+-------+-------+------------------+----------+---------+----------------------+------+-------------+ 

Answer 1

The MySQL optimizer performs a full table scan only if necessary, because the column may be NULL, which means that if the column is not defined as NOT NULL, it may contain some NULL values, and therefore MySQL should scan the table to find out. If your d.dog_id column is NULL? try running the counter in a different column that is not null, this should provide you with better performance than count (*).

Answer 2

Try setting the index to dogs.deceased and use SELECT COUNT(*) ... USE INDEX my_index_name .

Answer 3

Create indexes to speed up the count:

 CREATE INDEX ix_temp ON dogs (d.race_id) INCLUDE (columns needed for the query)