CODEWITHSUNDEEP
phpperformancesqlitepdocount
Alex
Alex

Reputation: 68036

SELECT count(column) slower than SELECT *

Two simple queries:

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=?)

Upvotes: 5

Views: 326

Answers (3)

DPC
DPC

Reputation: 624

The whole problem has been due to caching. The first request it had to fetch data from server and second request there was no need to.

Re run both codes interchanging both queries or running it multiple times and you will know that there is no difference.

Upvotes: 2

Your Common Sense
Your Common Sense

Reputation: 157893

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.

Upvotes: 3

duellsy
duellsy

Reputation: 8577

I imagine that it would be a case of it having to keep memory of the ids that have already been accounted for and also needs to check for NULL entries.

If you haven't already, I'd suggest adding an index on your some_column to speed that up too.

Upvotes: 1

Related Questions