MySQL Bugs: #42210: Partition pruning depends on number of partitions involved in a query

Bug #42210	Partition pruning depends on number of partitions involved in a query
Submitted:	20 Jan 2009 9:08	Modified:	27 Feb 2009 8:55
Reporter:	Denis Kukharev	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.1.28-rc, 5.1.30	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	failed, partitioning, pruning

Description:
There is a log table partitioned by days as follows:
'PARTITION BY LINEAR HASH (LogDay) PARTITIONS 366'.

When I execute a query with such a clause like 
'where LogDay  between dayofyear(now())-10 and dayofyear(now())-1' and examine how partition pruning works, it is OK.

I expected that there is no difference in how many days the selected period contains - pruning mechanism should pick only partitions matching the selected days.

But if I widen the day range so as it exceeds 10 days, the 'explain partitions' shows that all partitions are used!

For example: 
'between dayofyear(now())-11 and dayofyear(now())-1' or 'between dayofyear(now())-10 and dayofyear(now())', etc.

I reported about this in a mysql forum (http://sqlinfo.ru/forum) and was proved that this effect takes place in version 5.1.30 too and advised to report a bug here.

Thanks!

How to repeat:
create a table

CREATE TABLE `LogTable` (
  `LogDay` smallint(6) NOT NULL,
  `Url` varchar(4096) CHARACTER SET utf8 DEFAULT NULL,
  KEY `LogDay` (`LogDay`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LINEAR HASH (LogDay) PARTITIONS 366  */

insert into LogTable values (1,'adfad'),(2,'adfad'),(3,'adfad'),(4,'adfad'),(5,'adfad'),(6,'adfad'),(7,'adfad'),(8,'adfad'),(9,'adfad'),(10,'adfad'),(11,'adfad'),(12,'adfad');

explain partitions select Url from LogTable where LogDay between 1 and 10; 
-- (pruning OK)

explain partitions select Url from LogTable where LogDay between 1 and 11;
-- (pruning failed)

See also  BUG#33730

Denis, thanks for taking time to report this. 

The dependency is a documented property. At the moment we plan to increase it from 10 to 32 (see BUG#33730 for more details and pointer to the docs.).

The thing is, when the optimizer encounters partitioning on a non-analyzable funciton (e.g. HAHS(LogDay)) and a piece of WHERE clause in form

 LogDay  between dayofyear(now())-10 and dayofyear(now())-1

it processes process it by walking through

val1= dayofyear(now()) - 10 
val2= dayofyear(now()) - 10  + 1
val3= dayofyear(now()) - 10  + 2 
 . . .
valN= dayofyear(now()) - 10  + N = dayofyear(now())-1 

and seeing which partitions we hit. Apparently this approach will be too slow to be usable if we applied it for all BETWEEN predicates because someobody could write 

   partitioning_column BETWEEN const1 AND const  + 1,000,000

and that would take too long to analyze. So we've had to set a limit somewhere.

Ok, thanx for your kindly explanation. I have seen that similar bug (I couldn't find it in the base until I have posted my one :) )
And as far as I understood, one of the possible solutions was to make it a tunable parameter. Was it considered unacceptable?

I thank that adding a tunable server variable/hint for cases like this is a reasonable feature request.

Yeah, it'd be very convenient, 'cause in my case, for instance, I'd like to scan 2-week periods, but for now, I'm restricted (if I don't want to loose pruning) with 10 days. So, in some cases it may be critical to tune it right to the desired value which would be not too big and quite acceptable by the optimizer but not reachable in case of inappropriate pre-defined constant.

The problem is resolved by the fix for BUG#33730.

Unfortunately we could not put the fix into 5.1 (as it is only a performance issue and 5.1 is already GA), so it will be available only in 6.0.

If you really need it in 5.1, the patch in BUG#33730 (you need only the last one) should be apply to 6.0 without any adjustments.

Marking as duplicate of BUG#33730