Which is a less expensive query count(id) or order by id

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 b开发者_如何学Pythonefore 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.

---

If you

1. start from 1
2. never have any gaps
3. use the InnoDB engine
4. 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.

---

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.

---

As already pointed out in the comments, your table is so small it really doesn't what your solution will be. For this reason the select count(id) should be used as it expresses the intent and doesn't need any further processing.

Now select count(id) comes with an alternative select count(*). These two are not synonyms. select count(*) will count the number of rows and use a cached value if possible when select count(id) counts the number of non null values of the column id exists. If the id columns is set as not null then the cached row count may be used.

The selection between count(*) and count(id) depends once again on your intent. In the general case, count(*) describes the intent better.

The there is the possibility of count(1) which is actually a synonym of count(*) when using mysql but the interpretation may vary if end up using a different RDBMS.

The performance of each type of count also varies depending on whether you are using MyISAM or InnoDB. The row counts are cached on the former but not on the latter, if I've understood correctly.

In the end, you should rely on query plans and running tests and measuring their performance rather than these general ramblings.