MySQL Forums :: Partitioning :: Partitioning with range(to_days) issue


Advanced Search

Partitioning with range(to_days) issue
Posted by: Jools Lebowski ()
Date: October 29, 2009 11:13AM

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

Options: ReplyQuote


Subject Views Written By Posted
Partitioning with range(to_days) issue 7529 Jools Lebowski 10/29/2009 11:13AM
Re: Partitioning with range(to_days) issue 2931 Rick James 10/31/2009 12:00PM
Re: Partitioning with range(to_days) issue 2687 Jools Lebowski 11/02/2009 04:07AM
Re: Partitioning with range(to_days) issue 2586 Rick James 11/02/2009 10:22AM
Re: Partitioning with range(to_days) issue 2620 Jools Lebowski 11/03/2009 02:18AM
Re: Partitioning with range(to_days) issue 3277 Rick James 11/03/2009 09:32AM
Re: Partitioning with range(to_days) issue 2342 Jools Lebowski 01/13/2010 05:17AM
Re: Partitioning with range(to_days) issue 2252 Rick James 01/22/2010 11:09PM
Re: Partitioning with range(to_days) issue 2599 Jason Shoulet 11/05/2009 03:12AM
Re: Partitioning with range(to_days) issue 2597 Rick James 11/05/2009 08:37AM
Re: Partitioning with range(to_days) issue 3466 Jason Shoulet 11/05/2009 09:51AM
Re: Partitioning with range(to_days) issue 2515 Mattias Jonsson 11/17/2009 05:51AM
Re: Partitioning with range(to_days) issue 2595 Sarah Sproehnle 11/05/2009 07:47PM
Re: Partitioning with range(to_days) issue 2314 Jools Lebowski 01/13/2010 06:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.