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:
SELECT COUNT(id) FROM table WHERE dummy = 1; 50ms - 58936 rows Explain:
COUNT(id)may be slower than
COUNT(*). The former checks
NOT NULL; the latter simply counts the rows. (If
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
WHEREclause. In your example, any index with
idcan be used for the first, and any index with both
idfor 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