select count(*) taking considerably longer than select * for same "where" clause?

Question

I am finding a select count(*) is taking considerably longer than select * for the queries with the same where clause.

The table in question has about 2.2 million records (call it detailtable). It has a foreign key field linking to another table (maintable).

This query takes about 10-15 seconds:

select count(*) from detailtable where maintableid = 999

But this takes a second or less:

select * from detailtable where maintableid = 999

UPDATE - It was asked to specify the number of records involved. It is 150.

UPDATE 2 Here is information when the EXPLAIN keyword is used.

For the SELECT COUNT(*), The EXTRA column reports:

Using where; Using index

KEY and POSSIBLE KEYS both have the foreign key constraint as their value.

For the SELECT * query, everything is the same except EXTRA just says:

Using Where

UPDATE 3 Tried OPTIMIZE TABLE and it still does not make a difference.

Answer 1

For sure

select count(*)

should be faster than

select *

count(*), count(field), count(primary key), count(any) are all the same.

Your explain clearly stateas that the optimizer somehow uses the index for count(*) and not for the other making the foreign key the main reason for the delay.

Eliminate the foreign key.