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
