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

re: optimize query??

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" <brucexwxduncanx@hotmail.com> wrote in message
news:10r1d1hau0vsh7c@corp.supernews.com...[color=blue]
> Could someone please help to explain why the following query isn't using[/color]
the[color=blue]
> 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
>
>[/color]

re: optimize query??

Bruce D wrote:[color=blue]
> 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.[/color]

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.

re: optimize query??

"Bill Karwin" <bill@karwin.com> wrote in message
news:coqea102r2@enews1.newsguy.com...[color=blue]
>
> 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.[/color]

That makes sense. Thanks Bill!
-bruce

re: optimize query??

"Bruce D" <brucexwxduncanx@hotmail.com> wrote in message
news:10r1d1hau0vsh7c@corp.supernews.com...[color=blue]
> Could someone please help to explain why the following query isn't using[/color]
the[color=blue]
> 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
>[/color]
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

re: optimize query??

"Rich R" <rryan@cshore.com> wrote in message
news:qQssd.2832$w42.2660@newssvr31.news.prodigy.co m...[color=blue]
>[color=green]
> >[/color]
> 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
>[/color]
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

re: optimize query??

"Bruce D" <brucexwxduncanx@hotmail.com> wrote in message
news:10r8ns4glgnep4b@corp.supernews.com...[color=blue]
> "Rich R" <rryan@cshore.com> wrote in message
> news:qQssd.2832$w42.2660@newssvr31.news.prodigy.co m...[color=green]
> >[color=darkred]
> > >[/color]
> > There is no way you can optimize this query. I created a datbase where[/color][/color]
90%[color=blue][color=green]
> > of entries were 'MA'. The query demands a table scan . So one solution[/color][/color]
is[color=blue][color=green]
> > 'where zipcode between"
> > This works. But the better soloution is to create a table of Mass.[/color][/color]
stuff.[color=blue][color=green]
> > Not normalized nor a proper subtype, but runs real fast.
> >
> > Rich
> >[/color]
> 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[/color]
I[color=blue]
> 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[/color]

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