What is faster, counting the number of elements in a table or getting the last record?

What is faster, counting the number of elements in a table or getting the last record?

We are开发者_运维问答 talking about MySql here.

If the table is ISAM, row count is cached and should be returned instantly.

If the table is InnoDB, it can be a little slower.

For more information, see especially this comment:

http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/comment-page-1/#comment-106645

I don't know that these two actions really accomplish the same thing. What is it you're trying to do?

Are you trying to determine how many records are in the table? If so, simply getting the last record (and, I assume, using it's ID number as the number of records) wouldn't be accurate. If you've inserted 10 rows, and then you delete rows 3 and 7 then the highest ID is 10 but you only have 8 records.

For the same reasons, counting the records won't give you the highest ID number, because in many cases there will be fewer records than the number indicated by the highest ID.

Also, in terms of speed, I'm pretty sure most methods for counting rows and most methods for determining the last row will both use the table/indexes in similar ways, so I'm not thinking there'd be a huge speed difference.

Wild guess here, but provided your table is indexed properly, getting any element is extremely fast. I would think that getting the last element is faster and would even expect MySQL to actually count all the records when you ask for the record count.

Why not just use

Select count(*) FROM ...

That is probably the fastest way as the database can do it quickly and the amount of data returned is very small.