Subject Re: [ib-support] One line of WHERE clause slows query down
Author Doug Chamberlin
At 06/07/2002 11:50 AM (Friday), Stevio wrote:
>Why does this line increase the speed of the query so much? Is there a way
>to work around this so that I can still restrict the query to take records
>with a value of 1 but without the query taking so long?

You probably have indexes on the other fields which you are selecting on.
That usually helps makes the query go faster. However, your location field
is not very "selective" with respect to your table contents. Even if you
have an index on the location field, using that index to select specific
records for a given location value will not result in a small enough subset
for it to be efficient. Therefore, when you add that line the optimizer
probably concludes it would be faster to just read the whole table to
determine which records have the matching location.

You could create a compound index of location plus another field which is
unique to each record. This compound index would be highly selective and,
therefore, might be used more efficiently by the query optimizer.