MySQL not using index

Can someone explain to me why this simple query will not use an index
on the field confirm_date?

select * from comments where confirm_date != 0

confirm_date is an integer, and I have a regular index on it. When I
use EXPLAIN it shows all 1233 rows being searched with NULL for the
possible keys. Thanks for your help.

re: MySQL not using index

Boo wrote:[color=blue]
> Can someone explain to me why this simple query will not use an index
> on the field confirm_date?
>
> select * from comments where confirm_date != 0[/color]

MySQL's optimizer tries to make an informed guess at whether using an
index would result in a greater benefit than the extra cost of reading
the index itself.

In cases like your query, the optimizer might reasonably assume that
rows matching confirm_date != 0 is going to match such a high percentage
of the rows in the table that the "wasted" effort of reading a few extra
rows from the data file and discarding them will turn out to be less
than the cost of reading the index file in addition to the data file.

The optimizer isn't strictly guaranteed to make the correct decision,
but in the great majority of cases it seems to be on target.

Regards,
Bill K.

re: MySQL not using index

"Bill Karwin1" wrote:[color=blue]
> Boo wrote:[color=green]
> > Can someone explain to me why this simple query will not use[/color]
> an index[color=green]
> > on the field confirm_date?
> >
> > select * from comments where confirm_date != 0[/color]
>
> MySQL's optimizer tries to make an informed guess at whether
> using an
> index would result in a greater benefit than the extra cost of
> reading
> the index itself.
>
> In cases like your query, the optimizer might reasonably
> assume that
> rows matching confirm_date != 0 is going to match such a high
> percentage
> of the rows in the table that the "wasted" effort of reading a
> few extra
> rows from the data file and discarding them will turn out to
> be less
> than the cost of reading the index file in addition to the
> data file.
>
> The optimizer isn't strictly guaranteed to make the correct
> decision,
> but in the great majority of cases it seems to be on target.
>
> Regards,
> Bill K.[/color]

If all else fails, there is a way to force mysql to use an index.
Search for that.

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/mySQL-index-ftopict221773.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=763243

re: MySQL not using index

steve wrote:[color=blue]
> "Bill Karwin1" wrote:[color=green]
> > Boo wrote:[color=darkred]
> > > Can someone explain to me why this simple query will not use[/color]
> > an index[color=darkred]
> > > on the field confirm_date?
> > >
> > > select * from comments where confirm_date != 0[/color]
> >
> > MySQL's optimizer tries to make an informed guess at whether
> > using an
> > index would result in a greater benefit than the extra cost of
> > reading
> > the index itself.
> >
> > In cases like your query, the optimizer might reasonably
> > assume that
> > rows matching confirm_date != 0 is going to match such a high
> > percentage
> > of the rows in the table that the "wasted" effort of reading a
> > few extra
> > rows from the data file and discarding them will turn out to
> > be less
> > than the cost of reading the index file in addition to the
> > data file.
> >
> > The optimizer isn't strictly guaranteed to make the correct
> > decision,
> > but in the great majority of cases it seems to be on target.
> >
> > Regards,
> > Bill K.[/color]
>
> If all else fails, there is a way to force mysql to use an index.
> Search for that.
>[/color]
Steve,
Yes, you can force MySQL to use an index, but be careful for two
reasons; first it may be counter productive (e.g. the situation
Bill pointed out) and it can backfire on you when you upgrade to
a newer release with an improved optimizer.

Jerry

re: MySQL not using index

"jgitomer" wrote:[color=blue]
> steve wrote:[color=green]
> > "Bill Karwin1" wrote:[/color]
>  > > Boo wrote:
>   > > > Can someone explain to me why this simple
> query will not use
>  > > an index
>   > > > on the field confirm_date?
>   > > >
>   > > > select * from comments where confirm_date
> != 0
>  > >
>  > > MySQL's optimizer tries to make an informed guess
> at whether
>  > > using an
>  > > index would result in a greater benefit than the
> extra cost of
>  > > reading
>  > > the index itself.
>  > >
>  > > In cases like your query, the optimizer might
> reasonably
>  > > assume that
>  > > rows matching confirm_date != 0 is going to match
> such a high
>  > > percentage
>  > > of the rows in the table that the "wasted" effort
> of reading a
>  > > few extra
>  > > rows from the data file and discarding them will
> turn out to
>  > > be less
>  > > than the cost of reading the index file in addition
> to the
>  > > data file.
>  > >
>  > > The optimizer isn't strictly guaranteed to make the
> correct
>  > > decision,
>  > > but in the great majority of cases it seems to be
> on target.
>  > >
>  > > Regards,
>  > > Bill K.[color=green]
> >
> > If all else fails, there is a way to force mysql to use an[/color]
> index.[color=green]
> > Search for that.
> >[/color]
> Steve,
> Yes, you can force MySQL to use an index, but be careful for
> two
> reasons; first it may be counter productive (e.g. the
> situation
> Bill pointed out) and it can backfire on you when you upgrade
> to
> a newer release with an improved optimizer.
>
> Jerry[/color]

Jerry: Fair enuf. It makes sense to force an index when you know
mysql is making the wrong decision about the index it is picking, and
that can happen.

I guess we are getting off-thread with what the poster was asking, but
nonetheless good discussion.

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/mySQL-index-ftopict221773.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=763704

re: MySQL not using index

For some reason I changed the != 0 to > 0 and the index works now.
Weird.