SEO

#1 (**permalink**) 02-28-2008, 10:30 AM

Hi -

I'm wondering if someone can explain why mysql behaves like this, in
addition to hopefully solving my problem. I have a simple table,
orders:

recordid: int (primary index)
personid: int
dayid: int (secondary index)

If I do a simple query:

select * from orders where dayid = '100'

I expected to for this to be fast with the index, but it turns out
when I did an explain, that it's searching through way more rows than
I expected. Then, I looked at the structure, and the cardinality of
the index is a fraction of the number of records. So, why is this?
Can someone explain to me the way a secondary one-column index works
and why a query based exclusively on this column isn't fast (why is
the number of rows searched, not the number of unique values in the
table)?

Thanks,
Dino

#2 (**permalink**) 02-28-2008, 10:30 AM

"dino d." <dinodorroco@yahoo.com> schreef in bericht
news:c185d894-2992-4602-8bad-ed0657df0f6a@n20g2000hsh.googlegroups.com...
>
> Hi -
>
> I'm wondering if someone can explain why mysql behaves like this, in
> addition to hopefully solving my problem. I have a simple table,
> orders:
>
> recordid: int (primary index)
> personid: int
> dayid: int (secondary index)
>
> If I do a simple query:
>
> select * from orders where dayid = '100'
>
> I expected to for this to be fast with the index, but it turns out
> when I did an explain, that it's searching through way more rows than
> I expected. Then, I looked at the structure, and the cardinality of
> the index is a fraction of the number of records. So, why is this?
> Can someone explain to me the way a secondary one-column index works
> and why a query based exclusively on this column isn't fast (why is
> the number of rows searched, not the number of unique values in the
> table)?
>
> Thanks,
> Dino

it has something to do with: Cardinality
see: http://dev.mysql.com/doc/refman/5.0/en/show-index.html

you probably have a 'low' value on this...

#3 (**permalink**) 02-28-2008, 10:30 AM

> it has something to do with: Cardinality
> see:http://dev.mysql.com/doc/refman/5.0/en/show-index.html
>
> you probably have a 'low' value on this...

I do indeed have a low value on this. Thanks for the link, it says:

"The higher the cardinality, the greater the chance that MySQL uses
the index when doing joins."

This implies that mySQL uses some heuristic to determine when to use
an index. So, if I have a table where one column has a few unique
values, and another has a lot of unique values, and both are indexed,
the table with few unique values will take longer to do a query on
(doing a simple query involving only that column). This seems
counterintuitive. Is this because there is some overhead associated
with using the index, and searching through lots of rows is cheaper?
Thanks for your help.

Dino

#4 (**permalink**) 02-28-2008, 10:30 AM

On 11 Dec, 13:26, "dino d." <dinodorr...@yahoo.com> wrote:
> > it has something to do with: Cardinality
> > see:http://dev.mysql.com/doc/refman/5.0/en/show-index.html
>
> > you probably have a 'low' value on this...
>
> I do indeed have a low value on this. Thanks for the link, it says:
>
> "The higher the cardinality, the greater the chance that MySQL uses
> the index when doing joins."
> Is this because there is some overhead associated
> with using the index, and searching through lots of rows is cheaper?

Yes it may well be cheaper, but note that it specifically says when
using joins and your question did not contain a join.

Also, note this other part of the same paragraph:
"This is updated by running ANALYZE TABLE or myisamchk -a."

#5 (**permalink**) 02-28-2008, 10:30 AM

> Also, note this other part of the same paragraph:
> "This is updated by running ANALYZE TABLE or myisamchk -a."

Is ANALYZE for the purposes of displaying the best guess when using
EXPLAIN, or is it actually used when the queries are run. In other
words, must you from time to time run ANALYZE to ensure that mysql is
making best guesses on updated estimates of unique keys?

Thanks again,
Dino

#6 (**permalink**) 02-28-2008, 10:30 AM

"dino d." <dinodorroco@yahoo.com> schreef in bericht
news:5e205273-a1ea-4cbb-881a-1fcca0f33ebc@i72g2000hsd.googlegroups.com...
>
>> Also, note this other part of the same paragraph:
>> "This is updated by running ANALYZE TABLE or myisamchk -a."
>
> Is ANALYZE for the purposes of displaying the best guess when using
> EXPLAIN, or is it actually used when the queries are run. In other
> words, must you from time to time run ANALYZE to ensure that mysql is
> making best guesses on updated estimates of unique keys?
>
> Thanks again,
> Dino

what about:
http://dev.mysql.com/doc/refman/5.0/...yze-table.html

does this answer your question?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

secondary index question