I have a table with millions of rows. I need to find one document (WHERE DocumentName = 'xxx'). Table is being populated since last century. Will the query be faster if I include (AND Date > '2021-01-01') in a WHERE statement? Because I need only recent one records.
3 Answers
It should be faster, but there are many considerations.
The simplest is that returning less data is generally faster. The additional where conditions (which you describe as being connected by AND) might allow more optimizations as well -- particularly indexes or partitions, for instance.
That said, there is some overhead for the additional data check, which adds a little bit of overhead (but typically much, much less than returning more data). And more complicated where clauses can confuse the optimizer.
But most importantly: If you only need more recent records, you should write the query that returns what you really need.
I would say in most cases it will be faster but just to be sure you can check its effect by using SET SHOWPLAN_TEXT. Also, you can boost your query's performance by using indexes.
if you have an index on the DocumentName column, adding the filter on Date will not make any difference. adding that index is probably your best bet to speed up the query.
if you do not have index on DocumentName but have an index on Date, adding the where clause for Date will help significantly.
if you do not have index on either of the two columns, it may still help and in the worst case probably would not hurt, so probably keeps it (maybe test).
Related