How do I create effective date partitions?
Posted by:
avi weiss
Date: September 11, 2007 10:34AM
I have two duplicate databases setup in MySql 5.1.21. one called "usage" with no tables partitioned, and the other called "usage_np" with a partitioned table "pagelog". Each version of the pagelog table contains the same 54 million records
The table was partitioned using the following hash:
"alter table pagelog partition by hash (to_days(pl_datetime)) partitions 300"
Where pl_datetime is a datetime column ranging in values from '2004-09-01 00:00:00' to '2007-09-09 00:00:00'. There is also an index that uses pl_datetime on both partitioned and non-partitioned tables.
When I run "explain" on the following query:
"select site_id from pagelog where pl_datetime between '2007-06-01' and '2007-09-05'"
against each of the databases, I get some very counterintuitive results: specifically the query against the partitioned table scans all 54 Million records, where the query against the non-partitioned table scans a range of approx 9 Million:
partitioned table explain results:
1, 'SIMPLE', 'pagelog', 'ALL', 'pl_datetime', '', '', '', 54276525, 'Using where'
non-partitioned table explain results:
1, 'SIMPLE', 'pagelog', 'range', 'pl_datetime', 'pl_datetime', '8', '', 9012219, 'Using where'
Additionally, the query issued against both tables takes 41 seconds in both cases.
So what am I doing something wrong? Am I missing something? Is this an improper application of partitioning? Improper hash value? Is there something
Any constructive thoughts greatly appreciated.
-avi