MySQL Forums
Forum List  »  Partitioning

Re: How do I create effective date partitions?
Posted by: Mikael Ronström
Date: September 12, 2007 11:27AM

Hi,
The query will scan all partitions so there won't be any positive
impact of partitioning in this query. If you want partitioning here
to improve matters you should use PARTITION BY RANGE with the same
function and define a number of partitions (probably not as many 300
but more like 30 or so. Then the query will only scan a number of
those partitions and should hopefully execute in around 4-5 seconds
instead of 41.

Why EXPLAIN reports different number of rows I cannot explain :)

Rgrds Mikael


avi weiss Wrote:
-------------------------------------------------------
> 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
3995
September 11, 2007 10:34AM
Re: How do I create effective date partitions?
2914
September 12, 2007 11:27AM


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.