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...an d the top query
is taking 12 seconds...the bottom query is 1 second.

Any help is appreciated!
-bruce

[Bruce D]

Re: optimize query??

I should have mentioned...the re 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" <brucexwxduncan x@hotmail.com> wrote in message
news:10r1d1hau0 vsh7c@corp.supe rnews.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...an d the top query
> is taking 12 seconds...the bottom query is 1 second.
>
> Any help is appreciated!
> -bruce
>
>[/color]

[Bill Karwin]

Re: optimize query??

Bruce D wrote:[color=blue]
> I should have mentioned...the re 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.

[Bruce D]

Re: optimize query??

"Bill Karwin" <bill@karwin.co m> 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

[Rich R]

Re: optimize query??


"Bruce D" <brucexwxduncan x@hotmail.com> wrote in message
news:10r1d1hau0 vsh7c@corp.supe rnews.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...an d 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

[Bruce D]

Re: optimize query??

"Rich R" <rryan@cshore.c om> wrote in message
news:qQssd.2832 $w42.2660@newss vr31.news.prodi gy.com...[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

[Rich R]

Re: optimize query??


"Bruce D" <brucexwxduncan x@hotmail.com> wrote in message
news:10r8ns4glg nep4b@corp.supe rnews.com...[color=blue]
> "Rich R" <rryan@cshore.c om> wrote in message
> news:qQssd.2832 $w42.2660@newss vr31.news.prodi gy.com...[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