CODEWITHSUNDEEP
mysqloptimizationquery-optimization
AidOnline01
AidOnline01

Reputation: 737

Count with where is faster then without?

I have table with around 60 000 rows. I have this two queries that are drastically different in speed. Can you explain why?

SELECT COUNT(id) FROM table;
300ms - 58936 rows

Explain:
id select_type table partitions type possible_keys key key_length ref rows filtered Extra
1 SIMPLE table NULL index NULL table_id_index 8 NULL 29325 100.00 using index 
SELECT COUNT(id) FROM table WHERE dummy = 1;
50ms - 58936 rows

Explain:
id select_type table partitions type possible_keys key key_length ref rows filtered Extra
1 SIMPLE table NULL index NULL dummy_index 5 const 14662 100.00 using index

Upvotes: 1

Views: 169

Answers (1)

Rick James
Rick James

Reputation: 142296

It depends.

  • COUNT(id) may be slower than COUNT(*). The former checks id for being NOT NULL; the latter simply counts the rows. (If id is the PRIMARY KEY, then this is unlikely to make any measurable difference.)
  • The Optimizer may decide to scan the entire table rather than use an index.
  • The Optimizer may pick an irrelevant index if not forced to by the WHERE clause. In your example, any index with id can be used for the first, and any index with both dummy and id for the second.
  • If you run the same query twice, it may run much faster the second time due to caching. This can happen even for a 'similar' query. I suspect this is the "answer". I often see a speedup of 10x if the first run was from disk and the second found everything needed in cache (the buffer_pool).
  • To get more insight, do EXPLAIN SELECT ...
  • The optimal index for your second query is INDEX(dummy, id).

Upvotes: 3

Related Questions