optimize query??

[Bruce D]

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

[Bruce D]

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

[Bill Karwin]

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.

[Bruce D]

"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

[Rich R]

"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

[Bruce D]

"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

[Rich R]

"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