Adding Where clause takes less time in SQL - why?

Question

select count(*) 
from table 

takes more time to execute than:

select count(*) 
from table 
where x = '1' 
here x is not an index

Using where clause, SQL has to do some extra work so why the query having where clause is fast.

Answer 1

By passing a WHERE clause, you indicate your RDBMS that only a part of the table needs to be scanned to find the result. Without the WHERE clause, all the table needs to be scanned. That does not mean that one solution will always be faster than the other.

Whether one solution is faster than the other actually depends on multiple factors such as :

• table structure (for example, is there an index on the column in the WHERE clause),

• size (number of rows)

• data distribution in the filtered column

• accuracy of statistic data (were statistics recently computed for that table, with which sample size, ...)

Based on such factors, the optimizer of your RDBMS will decide which execution plan should be used, which will ultimately determine the query performance.

Answer 2

This would generally be because you have an index that includes the column x.

In general, count(*) requires scanning all the rows or scanning the smallest index (depending on the database).

By comparison, with your where clause, there are basically two approaches:

• Scan all the rows and perform the comparison.
• Look up the values in an index that meet the condition.

The second approach is generally much faster than the first.

Other factors can come into play. For instance, another process might be updating or inserting rows, which places locks on the the table or parts of the table. That can slow down other queries. In all likelihood, though, the cause is the presence of an index.