MySQL Forums
Forum List  »  Partitioning

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

Options: ReplyQuote


Subject
Views
Written By
Posted
How do I create effective date partitions?
3994
September 11, 2007 10:34AM


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.