SELECT count(column) slower than SELECT *

Question

Two simple queries:

• SELECT * FROM some_table WHERE some_column = 1

  Returns array with all the records, with columns and values. About 100K in total. Takes ~40ms to complete.

• SELECT COUNT(id) FROM some_table WHERE some_column = 1

  Returns just the record count value, same count as the query above. Takes 1 second!!

EXPLAIN QUERY PLAN tells me the same thing for both queries: that it's searching the table using index...

Am I missing something here? Why is getting the count slower than getting the actual records?

I really don't want to use the 1st query, because I just need the count, and fetching 100K records will surely use all available memory :(

EXPLAIN QUERY PLAN output:

query #1:

selectid    order   from    detail
0   0   0   SEARCH TABLE atts USING INDEX idxType (type=?)

query #2:

selectid    order   from    detail
0   0   0   SEARCH TABLE atts USING COVERING INDEX idxType (type=?)

Answer 1

So, as it turned out, there is no difference between these two queries - thus, there is no question at all.

Speaking of the overall timing - apparently you have to move from a toy-database to a real one.