Partitioning with range(to_days) issue

[Jools Lebowski]

Hi everyone.

we are trying to use MySQL partitions on a table with over 150 million rows. What interests us is to partition by month so we use to_days function (which according to many sources is THE solution for date partitioning).

Before we use partitions on the real table we are testing with a table containing a little less than 3 million rows.
Structure is as follows:

CREATE TABLE `testpart` (
`id_keyword` int(10) unsigned NOT NULL DEFAULT '0',
`id_annonce` int(10) unsigned DEFAULT NULL,
`dateImpression` datetime NOT NULL,
`position` tinyint(3) unsigned DEFAULT NULL,
`type` int(5) unsigned NOT NULL DEFAULT '0',
`nb_resultats` int(10) unsigned NOT NULL,
KEY `id_keyword` (`id_keyword`),
KEY `id_annonce` (`id_annonce`),
KEY `dateImpression` (`dateImpression`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(dateImpression))
(PARTITION p0 VALUES LESS THAN (733985) ENGINE = MyISAM, #2009-08-01
PARTITION p1 VALUES LESS THAN (734016) ENGINE = MyISAM, #2009-09-01
PARTITION p2 VALUES LESS THAN (734046) ENGINE = MyISAM, #2009-10-01
PARTITION p3 VALUES LESS THAN (734077) ENGINE = MyISAM, #2009-11-01
PARTITION p4 VALUES LESS THAN (734107) ENGINE = MyISAM, #2009-12-01
PARTITION p5 VALUES LESS THAN (734138) ENGINE = MyISAM, #2010-01-01
PARTITION p6 VALUES LESS THAN (734169) ENGINE = MyISAM, #2010-02-01
PARTITION p7 VALUES LESS THAN (734197) ENGINE = MyISAM) #2010-03-01*/

Uniqueness in this table is on columns id_keyword+id_annonce+dateImpression.

The following query works as expected:
EXPLAIN PARTITIONS SELECT count(*) FROM testpart WHERE dateImpression BETWEEN '2009-08-01 00:00:00' AND '2009-08-05 00:00:00'

Here's the result:
id |select_type |table |partitions |type |possible_keys |key |key_len |ref |rows |Extra
1 |SIMPLE |testpart |p1 |range |dateImpression |dateImpression |8 |NULL |25556 |Using where; Using index

This is fine as it only uses 26k rows.

When the date range exceeds five days things are different. Explain shows that MySQL will use the correct partition but will scan the entire table...

EXPLAIN PARTITIONS SELECT count(*) FROM testpart WHERE dateImpression BETWEEN '2009-08-01 00:00:00' AND '2009-08-11 00:00:00'

This gives us:

id |select_type |table |partitions |type |possible_keys |key |key_len |ref |rows |Extra
1 |SIMPLE |testpart |p1 |index |dateImpression |dateImpression |8 |NULL |2730592 |Using where; Using index

It does use only one partition (p1) but rows=2730592 which means it's scanning the entire table...

We've tried adding a partition at the very beginning with VALUES LESS THAN (0) (seen somewhere in the forums) and it didn't make any difference.
We also tried HASH(RANGE(to_days)) for testing purposes to no avail. Anyway, we do not want to use hash as we have less control over the partitions.

Any thoughts? What are we doing wrong?

Server version: 5.1.31-log

Thanks in advance for your help.

Jools