Faster to get one row from DB or count number of rows

Question

Hi,

I was wondering if its faster to retrieve the "count" of the number of rows or to retrieve just 1 row using limit. The purpose being to see whether theres any row when given certain Where conditions.

Answer 1

its faster to retrieve the "count" of the number of rows

Answer 2

A count is always an expensive query because it will take a full table scan. You requirements are not really clear to me, but if you just want to see whether there is any data it would be cheaper to do a regular select with a limit to 1.

Answer 3

It think it depends on which storage engine you use for the database...

Anywawy, the good practise to test wether there are or not results is to check the return value from the feth() function!

Answer 4

A count must physically count all rows that match your criteria, which is unnecessary work as you don't care about the number.

Look at using EXISTS.

Answer 5

[I'm using Oracle as an example here, but the same concepts apply usually across the board.]

COUNT has to physically identify all the rows that will be returned. Depending on the complexity of the query, the query plan could require a table scan on one or more of the tables of the query. You'd need to do an EXPLAIN PLAN to know for sure.

Returning a single row may require the same processing if an ORDER BY is required. The database can't just give you the first row until 1) all the rows are identified and 2) the rows have been sorted. Also, depending on the number of rows being returned, the complexity of the ORDER BY, and the SGA size, a temporary table might need to be created (which causes all sorts of other overhead).

If there's no ORDER BY, a single row should be faster because as soon as the data identifies a single row to return, it's done. That said, you're not guaranteed from one execution to the next that the rows are returned in the same order, so usually an ORDER BY is involved.