From: Rick James
Date: November 16 2012 8:13pm
Subject: RE: Query Optimization
List-Archive: http://lists.mysql.com/mysql/228645
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB14911AAAAB@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

It depends on the distribution of the 4 'values' in that field.
If the cardinality is poor, then INDEX(VAL) won't be used, and they will al=
l do a table scan.

> -----Original Message-----
> From: Anupam Karmarkar [mailto:sb_akarmarkar@stripped]
> Sent: Friday, November 16, 2012 12:36 AM
> To: mysql@stripped
> Subject: Query Optimization
>=20
> Hi All,
>=20
> Consider a scenario, I have table XYZ which contains value follow BLUE RE=
D
> GREEN NULL
>=20
> following are queries we can use get this values
>=20
> 1. SELECT * FROM XYX WHERE VAL IN ('BLUE','RED','GREEN'); 2. SELECT * FRO=
M
> XYZ WHERE VAL IS NOT NULL 3. SELECT * FROM XYZ WHERE VAL =3D 'BLUE' OR
> VAL=3D'RED' OR VAL=3D'GREEN'
> and more
>=20
> So which one is good in terms of optimization. I guess, 1 and 3 are simil=
ar
> in term of formation.
>=20
>=20
> --Anupam