I realize that this thread is three days old, but I’m going to attack this anyway because it’s my area of expertise and my logic goes against what some people on this thread have said.
With the databases I am familiar with (in order: Sybase, SQL Server, MySQL, and, to a much lesser extent, Oracle), with a single table SELECT like in your examples, there can be one of three scenarios:
- The table has a single index that includes all the columns you are filtering.
- The table has an index on some of the columns, but not all of them
- The table has no index on these columns.
In each case, however, the outcome is the same: the more filtering you have, the less load will be put on the server (this is a assuming a disk-based database; if in-memory, the conclusion would be a bit different).
This may seem counter-intuitive, but hear me out:
First of all, you need to remember that disk I/O is several orders of magnitude slower than in-memory operations, so in the case of a database where many thousands of disk pages are involved, the amount of time to do memory operations can be considered to be insignificant, relative to the time spent doing I/O.
Now, let’s take case 1: You have an index covering all columns in your WHERE clause. The database engine traverses the index and retrieves only the rows you need. If your criteria are less narrow, there are more rows to be read from disk and more to send back to the client.
Case 2: The database traverses the index, retrieves all rows that match the indexed columns, then checks each retrieved row against the non-indexed columns and sends the relevant ones back. If you widen your criteria and choose to select only on indexed columns, the number of rows retrieved from disk is the same, but you still need to send more data back to the client, which is more expensive than doing in-memory comparisons.
Case 3: The database has to read each row in from disk and then send only the matching ones back to the client. Again, if you use wider criteria, you’re reading the same number of rows from disk, but you’re still putting a heavy load on the DB to send that data back.
Running with Scissors, DBA