March 27, 2011
Which is a less expensive query count(id) or order by id
Question by Alocus
I’d like to know which of the followings would execute faster in MySQL database. The table would have 200 – 1000 entries.
SELECT id
from TABLE
order by id desc
limit 1
or
SELECT count(id)
from TABLE
The story is the Table is cached. So this query is to be executed every time before cache retrieval to determine whether the cache data is invalid by comparing the previous value.
So if there exists a even less expensive query, please kindly let me know. Thanks.
Answer by RichardTheKiwi
If you
- start from 1
- never have any gaps
- use the InnoDB engine
- id is not nullable
Then the 2nd could run [ever so marginally] faster due to not having to visit table data at all (count is stored in metadata).
Otherwise,
- if the table has NO index on ID (causing a SCAN), the 2nd one is faster
Barring both the above
- the first one is faster
And if you actually meant to ask
SELECT .. LIMIT 1 vs SELECT MAX(id).. then the answer is actually that they are the same for MySQL and most sane DBMS, whether or not there is an index.
Answer by Starx
I think, the first query will run faster, as the query is limited to be executed for one row only, 200-1000 may not matter that much in this case.
