EXPERT RESPONSE
There are two things superficially wrong. DISTINCT is virtually
certain to be needless, in that there will not be any duplicate
rows in the result set (unless there are duplicate rows in your table, which
implies that it does not have a primary key, which every self-respecting
relational database table must have, or we wouldn't even be
having this conversation). DISTINCT requires a full sort on
all columns. Very expensive in performance, especially if it
won't remove any rows.
Secondly, the dreaded, evil "select star" is bad. List only the
columns you need (even if you need them all, I always say).
The main thing you need to do is to ensure that column being searched
has an index.
Also troubling is your remark that the user is being asked to make
a selection of "several hundred" values. Picturing the dropdown widget
or series of checkboxes that the user will have to deal with is making
me queasy. If it's true, send the form back to your user interface
analysts and ask them to get real. If you are the user interface
analyst, you need to redesign the form.
The only other thing that might be going on here is that you actually
have two queries. The first might be something as innocuous as:
select product_id from products
where product_category = "hubcaps"
Then you take the results of this query, serialize the values, and
use them to construct the IN list in the SQL query which you asked for
help with. If this is the case, you should use a single
join query instead of the two-query IN list approach.
|
