Re: A first table partitioning exercise.

[Mattias Jonsson]

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.