0

Does providing more than one filter condition in a WHERE-clause reduce the search time or is it like, since it processes more than one filter at a time, it increases the amount of processing required and so increases the execution time?

E.g.

select * from table_name where this = foo and that = bar

vs.

select * from table_name where this = foo

Adrian Frühwirth
  • 42,970
  • 10
  • 60
  • 71
Rishant
  • 9
  • 6
  • Depends on many factors and either can go either way. A filter viewed by a human can become a slow tablescan for instance. – Drew Jul 13 '15 at 02:54
  • Can you point me to any resource where i can read more on it. Thanks for replying :) – Rishant Jul 13 '15 at 02:59
  • http://stackoverflow.com/questions/12925140/how-to-estimate-sql-query-timing – Drew Jul 13 '15 at 03:02
  • http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/ – Drew Jul 13 '15 at 03:03
  • There are no lack of examples of 7 minute queries on The Stack that bring back 100 rows – Drew Jul 13 '15 at 03:18

1 Answers1

2

In general, the amount of time taken for the queries that you describe is based on the I/O time for reading the data and the time for returning the result set. The amount of time spent determining whether or not a record is in the result set is negligible, compared to the times for reading and returning records.

This is a general overview. If you have indexes on the where clauses, then the two constructs are basically the same. There is probably a very, very minor amount of additional overhead for the comparison on the second key, but that will not make a difference for real world applications.

Another difference from the general situation is when comparisons do take a long time. This might be due to like on a large text field or a complicated user-defined function. In that case, the comparison could make a difference.

More fundamentally, though, the queries do different things. You should express the query that you really need, before worrying about arcane performance characteristics. For instance, it is usually much better to do filtering in the database than to bring the data back to the application and filter there.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786