MySQL Forums
Forum List  »  Partitioning

Re: A first table partitioning exercise.
Posted by: Mattias Jonsson
Date: December 04, 2010 05:38PM

p0 is empty since there is no date less than 1995-01-01.
p11 is empty since there is no date on or after 2010-01-01.

The distribution should be the same since the procedure uses rand() with the same seeds (increase by one for each iteration).

The insert order does not really matter for this, but if it is inserted to the partitioned table first and then to the non partitioned table, it will be read from the partitioned table one partition at a time and therefore sorted by year in the non partitioned table (which don't give any gains in this particular test, and also note that there are no indexes which would give completely different results).

The reason I don't understand why the partitioned table is about 3 seconds when the non partitioned table takes 5 seconds is:
The query is a full table scan, which in the partitioned case can be pruned to only scan one partition, so since it is a lot of rows, all other overhead would be very small compared to do 'read next row' + 'compare the row with the condition'. So it is comparable with grep'ing a 8 000 000 row text file vs a 800 000 row text file (non partitioned table vs one partition). Even if we take RAM into the equation, the smaller sized file (partition) have higher chance to be cached than the larger file (full table).

Can you do the queries 10 times for the non-partitioned table and then 10 times for the partitioned table?

If it was only one time the query was 'off' then it could probably be explained by something outside of the MySQL server.

Options: ReplyQuote

Written By
December 02, 2010 11:00PM
Re: A first table partitioning exercise.
December 04, 2010 05:38PM

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.