On Wed, Jul 11, 2012 at 5:30 PM, Sergei Petrunia <pser...@askmonty.org> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
