473,320 Members | 1,580 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 5105
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Lenz Grimmer | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, MySQL 4.0.14, a new version of the popular Open Source/Free Software Database, has been released. It is now available in source and binary...
0
by: I.P. | last post by:
Hi, it's my story. I have two 4.0.14 mysql server on one machine with win XP Professional polish version. First acts as master: on port 3300 Second acts as slave: on port 3301 below my...
0
by: I.P. | last post by:
No one has replied to my post. ----- Original Message ----- From: "I.P." <jancio_wodnik@wp.pl> To: <mysql@lists.mysql.com> Sent: Monday, August 18, 2003 1:01 PM Subject: mysql 4.0.14 +...
0
by: bruce | last post by:
Hi... Update.... We have the following setup in our httpd.conf file. We've tried to give what's related to the issue. We're trying to set up a virtual host for a test project. The behavior...
4
by: jy2003 | last post by:
I have read a book, which suggests we should change OR to UNION for better performance, but currently I have too many OR clauses(I have a query with 100 ORs) and it does not sound good to have 100...
39
by: Mairhtin O'Feannag | last post by:
Hello, I have a client (customer) who asked the question : "Why would I buy and use UDB, when MySql is free?" I had to say I was stunned. I have no experience with MySql, so I was left sort...
1
by: Good Man | last post by:
Hi there I've noticed some very weird things happening with my current MySQL setup on my XP Laptop, a development machine. For a while, I have been trying to get the MySQL cache to work....
5
by: news.telia.net | last post by:
Hi! I have a question. I have installed php and mysql on an apache-server on windows and I can't connect to the server. I tried to create a database (since I am trying to learn howto). My...
8
by: The Natural Philosopher | last post by:
This is so weird. What I am trying to do is to upload files and stuff them in a mysql database. Everything works except the file content is zero. using the load_file command from mysql...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.