Performance: Select count(*) where... VS select column_name where

Question

I'll keep the scenario simple:

• There is an app, and there is a database.
• In the database, there is a table with a large amount of records
• I feed the app these records regularly

Now, one reload can imply some of these changes: i. New records in set ii. Updated records in set iii. Deleted records in set

Finally, for records that remain the same I can make use of a diferent process that works as much as 100 times faster than reading the table through ODBC.

Knowing this, I am trying to reduce the reloading time by using the ODBC as little as possible and midway through my algorithm I encounter a question that I have not been able to solve.

**How much faster is a select count(*) where [condition] opposed to select columnName where [condition]

Why? Because just by reloading the new and updated records, I can calculate whether there have been deletions or not by counting records depending on [condition]. Then, if there have been deletions, I'll have to read from the database those that remain. Nornally, the amount of new and updated records will be a small portion of the whole set. If a count is considerably faster than a normal select, I might want to use it as a fast check, since deleting records is not a common activity.

Answer 1

It really depends on a database you use while executing the query and gathered statistics.

1. Oracle DB. If stats have already been gathered , the first query would return the amount of rows in 0.00001 sec. If have not been, then it will be faster in five more times than while executing the second query as it is retrieving large amount of rows instead of just one.
2. For MS SQL will be almost the same thing.
3. For MySQL or MariaDB it depends on the engine. But I assume these databases always try caching the value of amount of rows as the optimizer will do queries faster.

In addition, columns in WHERE clause and in COUNT() do matter as well. They could be indexes or not and etc. There are lot of criteria considering perfomance between the queries but in the most cases the first one is recommended for using to get amount of rows. I could tell you more precisely if I had certain example.