469,623 Members | 1,969 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

optimize query??

Could someone please help to explain why the following query isn't using the
index...

explain select id from kbm where state = 'MA'

table type possible_keys key key_len ref rows Extra
kbm ALL State NULL NULL NULL 1000000 Using where

The field 'State' is Char(2). It has an index.

This query works great:
explain select id from kbm where Zipcode = '01001'

table type possible_keys key key_len ref rows Extra
kbm ref Zipcode Zipcode 5 const 9828 Using where

The field 'Zipcode' is Varchar(5). It has an index.

I'm confused. I'm using these queries with ColdFusion...and the top query
is taking 12 seconds...the bottom query is 1 second.

Any help is appreciated!
-bruce
Jul 20 '05 #1
6 1918
I should have mentioned...there are 1 million rows in the table kbm.
The "State = 'MA'" query returns 997,999 rows. The "Zipcode = '01001'"
query returns 15,673.
I have no idea if this even matters.

-bruce
"Bruce D" <br*************@hotmail.com> wrote in message
news:10*************@corp.supernews.com...
Could someone please help to explain why the following query isn't using the index...

explain select id from kbm where state = 'MA'

table type possible_keys key key_len ref rows Extra
kbm ALL State NULL NULL NULL 1000000 Using where

The field 'State' is Char(2). It has an index.

This query works great:
explain select id from kbm where Zipcode = '01001'

table type possible_keys key key_len ref rows Extra
kbm ref Zipcode Zipcode 5 const 9828 Using where

The field 'Zipcode' is Varchar(5). It has an index.

I'm confused. I'm using these queries with ColdFusion...and the top query
is taking 12 seconds...the bottom query is 1 second.

Any help is appreciated!
-bruce

Jul 20 '05 #2
Bruce D wrote:
I should have mentioned...there are 1 million rows in the table kbm.
The "State = 'MA'" query returns 997,999 rows. The "Zipcode = '01001'"
query returns 15,673.
I have no idea if this even matters.


It does matter. Since the query returns 99.8% of the rows in the table
anyway, MySQL has decided that using the index would only be extra work
with very little gain.

http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html says:

"Sometimes MySQL will not use an index, even if one is available. One
way this occurs is when the optimizer estimates that using the index
would require MySQL to access a large percentage of the rows in the
table. (In this case, a table scan is probably much faster, because it
will require many fewer seeks.)"

Regards,
Bill K.
Jul 20 '05 #3
"Bill Karwin" <bi**@karwin.com> wrote in message
news:co********@enews1.newsguy.com...

It does matter. Since the query returns 99.8% of the rows in the table
anyway, MySQL has decided that using the index would only be extra work
with very little gain.

http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html says:

"Sometimes MySQL will not use an index, even if one is available. One
way this occurs is when the optimizer estimates that using the index
would require MySQL to access a large percentage of the rows in the
table. (In this case, a table scan is probably much faster, because it
will require many fewer seeks.)"

Regards,
Bill K.


That makes sense. Thanks Bill!
-bruce
Jul 20 '05 #4

"Bruce D" <br*************@hotmail.com> wrote in message
news:10*************@corp.supernews.com...
Could someone please help to explain why the following query isn't using the index...

explain select id from kbm where state = 'MA'

table type possible_keys key key_len ref rows Extra
kbm ALL State NULL NULL NULL 1000000 Using where

The field 'State' is Char(2). It has an index.

This query works great:
explain select id from kbm where Zipcode = '01001'

table type possible_keys key key_len ref rows Extra
kbm ref Zipcode Zipcode 5 const 9828 Using where

The field 'Zipcode' is Varchar(5). It has an index.

I'm confused. I'm using these queries with ColdFusion...and the top query
is taking 12 seconds...the bottom query is 1 second.

Any help is appreciated!
-bruce

There is no way you can optimize this query. I created a datbase where 90%
of entries were 'MA'. The query demands a table scan . So one solution is
'where zipcode between"
This works. But the better soloution is to create a table of Mass. stuff.
Not normalized nor a proper subtype, but runs real fast.

Rich
Jul 20 '05 #5
"Rich R" <rr***@cshore.com> wrote in message
news:qQ*****************@newssvr31.news.prodigy.co m...

There is no way you can optimize this query. I created a datbase where 90%
of entries were 'MA'. The query demands a table scan . So one solution is
'where zipcode between"
This works. But the better soloution is to create a table of Mass. stuff.
Not normalized nor a proper subtype, but runs real fast.

Rich

Thanks for the responses!
I'm running into another very similar problem...
This query runs great (returns 218,973 records)
select count(*) as counter from kbm where State = 'MA' and DateofBirth >=
'19630701'
doing an explain shows that it is using the indexes.
But this query runs slow and does not use the indexes (returns 220,185
records)
select count(*) as counter from kbm where State = 'MA' and DateofBirth >=
'19630630'

You've both stated that there MySQL decides not to use an index...but can I
force it to? The second query is returning in 11 seconds in CF...not
exceptable.

Any ideas on how I can optimize (if at all)??

TIA
-bruce
Jul 20 '05 #6

"Bruce D" <br*************@hotmail.com> wrote in message
news:10*************@corp.supernews.com...
"Rich R" <rr***@cshore.com> wrote in message
news:qQ*****************@newssvr31.news.prodigy.co m...
There is no way you can optimize this query. I created a datbase where 90% of entries were 'MA'. The query demands a table scan . So one solution is 'where zipcode between"
This works. But the better soloution is to create a table of Mass. stuff. Not normalized nor a proper subtype, but runs real fast.

Rich

Thanks for the responses!
I'm running into another very similar problem...
This query runs great (returns 218,973 records)
select count(*) as counter from kbm where State = 'MA' and DateofBirth >=
'19630701'
doing an explain shows that it is using the indexes.
But this query runs slow and does not use the indexes (returns 220,185
records)
select count(*) as counter from kbm where State = 'MA' and DateofBirth >=
'19630630'

You've both stated that there MySQL decides not to use an index...but can

I force it to? The second query is returning in 11 seconds in CF...not
exceptable.

Any ideas on how I can optimize (if at all)??

TIA
-bruce


You just can't. I specified 'force indexes' but no go. And it's a smart idea
But when you think about what I did, it made no sense. The indexes were way
too dense. So a table scan is more efficient. I still recommend breaking out
'MA" into it's own table. 'MA" is 90% of your index. I'd do a table scan,
too!

Rich
Jul 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Andreas Falck | last post: by
5 posts views Thread by xeqister | last post: by
3 posts views Thread by Reddy | last post: by
4 posts views Thread by Huaer.XC | last post: by
13 posts views Thread by Frank Swarbrick | last post: by
1 post views Thread by acornejo | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.