Difference between IN and =
Do I face a speed penalty if I use IN for a single value in a
WHERE clause???
i.e.
SELECT * FROM PEOPLE WHERE last_name IN ('FRIEST');vrs
SELECT * FROM PEOPLE WHERE last_name = 'FRIEST';
I'm writing some generic routines which will substitute values into a
SELECT statement
and I was wondering if I should worry about switching the 1 value casesto = instead of just
leaving them as IN...
The above might be generated from
SELECT * FROM PEOPLE WHERE last_name IN (%1);where %1 could expand to any number of last names...
If there isn't much of a performance hit for using IN instead of =, it's
easier for me to just always
use IN....
--
Timothy Friest - Science Application International Corp (SAIC)
Anchorage Alaska
907-272-7105
In article <36D5F3D5.F8B18D41@saic.alaska.net>, Tim Friest
<friest@saic.alaska.net> writes
>Do I face a speed penalty if I use IN for a single value in a
>WHERE clause???
>
>i.e.
> SELECT * FROM PEOPLE WHERE last_name IN ('FRIEST');>vrs
> SELECT * FROM PEOPLE WHERE last_name = 'FRIEST';>
>I'm writing some generic routines which will substitute values into a
>SELECT statement
>and I was wondering if I should worry about switching the 1 value cases>to = instead of just
>leaving them as IN...
>
>The above might be generated from
> SELECT * FROM PEOPLE WHERE last_name IN (%1);>where %1 could expand to any number of last names...
>
>If there isn't much of a performance hit for using IN instead of =, it's
>easier for me to just always
>use IN....
>
IN (and also OR) will usually not use indexes.
Instead use unions
select * from people where last_name = "A"
union
select * from people where last_name = "B"
union
select * from people where last_name = "C"
This also has the advantage that the engine can do each part of the
union in parallel..
>--
>Timothy Friest - Science Application International Corp (SAIC)
>Anchorage Alaska
>907-272-7105
>
>
>
--
David Williams
David Williams wrote:
> In article <36D5F3D5.F8B18D41@saic.alaska.net>, Tim Friest
> <friest@saic.alaska.net> writes
> >Do I face a speed penalty if I use IN for a single value in a
> >WHERE clause???
> >
> >i.e.
> > SELECT * FROM PEOPLE WHERE last_name IN ('FRIEST');> >vrs
> > SELECT * FROM PEOPLE WHERE last_name = 'FRIEST';> >
> >I'm writing some generic routines which will substitute values into a
> >SELECT statement
> >and I was wondering if I should worry about switching the 1 value cases> >to = instead of just
> >leaving them as IN...
> >
> >The above might be generated from
> > SELECT * FROM PEOPLE WHERE last_name IN (%1);> >where %1 could expand to any number of last names...
> >
> >If there isn't much of a performance hit for using IN instead of =, it's
> >easier for me to just always
> >use IN....
> >
>
> IN (and also OR) will usually not use indexes.
Starting with 7.3 the optimizer is more intelligent about choosing a query
path. I.e. indexes should be used both with IN and OR where applicable.
Regards, Heiko
>
>
> Instead use unions
>
> select * from people where last_name = "A"
> union
> select * from people where last_name = "B"
> union
> select * from people where last_name = "C">
> This also has the advantage that the engine can do each part of the
> union in parallel..
>
>
> >--
> >Timothy Friest - Science Application International Corp (SAIC)
> >Anchorage Alaska
> >907-272-7105
> >
> >
> >
>
> --
> David Williams