Hi Simon,
thanks for your response. So, would it be wise to replace the multi-column
index (a,d,b) with (a,d) to avoid the overhead you mentioned when accessing
data from a+b??
Thanks again,
Tobias.
-----Ursprüngliche Nachricht-----
Von: Simon Windsor [mailto:[EMAIL PROTECTED]]
Gesendet: Mittwoch, 28. Februar 2001 12:11
An: [EMAIL PROTECTED]
Betreff: Re: EXPLAIN with unexpected result.
Hi
Two points:
- Any multi column index a,b,c also indexes a and a,b
- In using an index, efficiency is important, and if you have two indexes
a,b,c and a,d,b , when you hit data
from columns a+b+d, the database engine has to work out if accessing the
data from a+b is more efficient
than a+d.
If column d has very little variance, say 10 distinct values in 1000000
records ( eg 1000000 in this case), it is a poor
column to index, and would compare badly to other distributions. Standard
indexes perform very well for <5%
(eg 5000 in this case) hits.
Simon
On Wednesday 28 February 2001 10:20, you wrote:
> Hello,
> I'm slightly stunned seeing a result from the EXPLAIN statement.
>
> I have a table "T" with columns "a", "b", "c", "d" and a multi-column
index
> on (a, b, c)
>
> I perform a query:
>
> EXPLAIN SELECT * FROM T WHERE a = <some_value>, d = <some_value>, b =
> <some_value>
>
> The result tells me that MySQL is taking index (a, b, c). Why?
>
> Furthermore, when I add another multi-column index (a, d, b) to match the
> query above, EXPLAIN still tells me that it uses index (a, b, c).
>
> Can someone enlighten my on this one???
> Thanks,
> Tobias.
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]> Trouble
> unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
Simon Windsor
CricInfo http://www.cricinfo.com/
Tel: +44 (0) 1249 700744
Fax: +44 (0) 1249 700725
Email: mailto:[EMAIL PROTECTED]
This email message is for the sole use of the intended recipient(s) and may
contain
confidential and privileged information. Any unauthorized review, use,
disclosure or
distribution is prohibited. If you are not the intended recipient, please
contact the
sender by reply email and destroy all copies of the original message. Thank
you for your
cooperation and assistance.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
