List:General Discussion« Previous MessageNext Message »
From:Jeffrey Grollo Date:July 12 2012 4:30pm
Subject:Re: Composite Index Usage in Joins
View as plain text  
On Wed, Jul 11, 2012 at 5:30 PM, Sergei Petrunia <psergey@stripped> wrote:
> I can provide a refutation. Ability to make a combined index access of
>
> 1. Equality with a non-constant: t.sec_id= p.sec_id
> 2. non-equality comparison with constants, trade_time IN ('2012-07-01',
> '2012-07-02')
>
> has been discussed a number of times by query optimizer developer, but as far
> as public knowlege goes, nobody has ever implemented it, either at Oracle, or
> at MariaDB, or elsewhere.

As mentioned in my reply to Shawn, given the nested-loop join
processing, I had expected #1 to be treated like a constant for
purposes of index access, but sounds like this is either a
misunderstanding or just a limitation of how the optimizer builds an
execution plan.

My takeaway then is that composite indexes built for supporting join
and where criteria in a query should be constructed to speed join key
lookup first then to satisfy equality conditions in additional
criteria.

> MariaDB 5.3+ and MySQL 5.6+ have Index Condition Pushdown feature

A very nice feature indeed.

> Basically, ref access can be constructed from equality conditions. If you have
> data for many years, and the range you're scanning fits within one particular
> year, this could help:
>
> alter table trades add trade_year int, add index(sec_id, trade_year)
> update trades set trade_year=year(trade_date); -- will need to be done for new data,
> too

Appreciate the suggestion. Given the variability in date ranges this
search is over, finding the right granularity for supporting an
equality condition is an interesting problem. Presumably, the same
technique could be applied monthly, for example, with a second
composite index to support:

alter table trades add trade_month int, add index(sec_id, trade_month)

It seems that partition pruning might be another way to deal with
reducing the data scanned. With yearly partitions, I could then rely
on the storage engine only scanning relevant partitions and then use
equality at the month level.

Thank you for the insight,
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