469,293 Members | 1,335 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,293 developers. It's quick & easy.

MySQL not using index

Boo
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.

Jul 23 '05 #1
5 4802
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.
Jul 23 '05 #2
"Bill Karwin1" wrote:
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.


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
Jul 23 '05 #3
steve wrote:
"Bill Karwin1" wrote:
> 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.


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

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
Jul 23 '05 #4
"jgitomer" wrote:
steve wrote:
"Bill Karwin1" wrote:

 > > 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.

If all else fails, there is a way to force mysql to use an

index.
Search for that.

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


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
Jul 23 '05 #5
Boo
For some reason I changed the != 0 to > 0 and the index works now.
Weird.

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Lenz Grimmer | last post: by
reply views Thread by bruce | last post: by
4 posts views Thread by jy2003 | last post: by
39 posts views Thread by Mairhtin O'Feannag | last post: by
5 posts views Thread by news.telia.net | last post: by
8 posts views Thread by The Natural Philosopher | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.