List:General Discussion« Previous MessageNext Message »
From:Jeffrey Grollo Date:July 12 2012 4:06pm
Subject:Re: Composite Index Usage in Joins
View as plain text  
Thanks for the guidance and references, Shawn.

On Wed, Jul 11, 2012 at 3:37 PM, Shawn Green <shawn.l.green@stripped> wrote:
>
> Ranged scans only happen for the last portion of an index being used. OR
> queries (or those using IN) can also only be applied to the last part of an
> index search. This means that if you are using IN (or OR) against the first
> part of an index, that's where the usage of the index stops.

Basically, this is what I was expecting. However, in the second table
in the join, the secondary column wasn't being used for the range or
IN. I was expecting, given the nested-join loop algorithm (as I don't
believe that BNL would be used
http://dev.mysql.com/doc/refman/5.5/en/nested-loop-joins.html), that
the first column in the key would be treated as const allowing the
second column to be used in a range or IN criteria.

>
> Also, trying to force index usage may be creating more work for your disks
> than necessary.

I should have indicated in the original post that I was forcing the
index simply to make the example work without data.

Regards,
Jeff
Thread
Composite Index Usage in JoinsJeffrey Grollo10 Jul
  • Re: Composite Index Usage in JoinsShawn Green11 Jul
    • Re: Composite Index Usage in JoinsJeffrey Grollo12 Jul
  • Re: Composite Index Usage in JoinsSergei Petrunia11 Jul
    • Re: Composite Index Usage in JoinsJeffrey Grollo12 Jul
  • RE: Composite Index Usage in JoinsRick James13 Jul