Re: How do I create effective date partitions?
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