Re: A first table partitioning exercise.
Really strange, I have tested both 5.5.5-m3-winx64 and 5.5.7-rc on a windows machine (Server 2008, Optron 2.8 GHz x 2, 4GB RAM) with consistent results like the ones I reported before.
I cannot understand how your query on the partitioned myisam table can take ~3 seconds when the non partitioned myisam takes ~5 seconds. Is this repeatable on any other machine or setup? If you can help me repeat this bad performance I can look into it and probably fix it, but currently I cannot even repeat it.
Could you also do a 'SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, PARTITION_EXPRESSION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test';' to show that the rows are distributed like this:
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, PARTITION_EXPRESSION FROM
INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test';
+-------------+----------------+------------+----------------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | PARTITION_EXPRESSION |
+-------------+----------------+------------+----------------------+
| no_part_tab | NULL | 8000000 | NULL |
| part_tab | p0 | 0 | year(c3) |
| part_tab | p1 | 798458 | year(c3) |
| part_tab | p2 | 801766 | year(c3) |
| part_tab | p3 | 799590 | year(c3) |
| part_tab | p4 | 799553 | year(c3) |
| part_tab | p5 | 799538 | year(c3) |
| part_tab | p6 | 801742 | year(c3) |
| part_tab | p7 | 799579 | year(c3) |
| part_tab | p8 | 799586 | year(c3) |
| part_tab | p9 | 799557 | year(c3) |
| part_tab | p10 | 800631 | year(c3) |
| part_tab | p11 | 0 | year(c3) |
+-------------+----------------+------------+----------------------+
13 rows in set (0.00 sec)
This is the output of my script (note that I'm loading into the non partitioned table first to have more realistic data):
mysql> CREATE TABLE part_tab
-> ( c1 int default NULL,
-> c2 varchar(30) default NULL,
-> c3 date default NULL
-> ) engine=myisam
-> PARTITION BY RANGE (year(c3))
-> (PARTITION p0 VALUES LESS THAN (1995),
-> PARTITION p1 VALUES LESS THAN (1996),
-> PARTITION p2 VALUES LESS THAN (1997) ,
-> PARTITION p3 VALUES LESS THAN (1998),
-> PARTITION p4 VALUES LESS THAN (1999) ,
-> PARTITION p5 VALUES LESS THAN (2000),
-> PARTITION p6 VALUES LESS THAN (2001) ,
-> PARTITION p7 VALUES LESS THAN (2002),
-> PARTITION p8 VALUES LESS THAN (2003) ,
-> PARTITION p9 VALUES LESS THAN (2004),
-> PARTITION p10 VALUES LESS THAN (2010),
-> PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.10 sec)
mysql>
mysql> create table no_part_tab
-> (c1 int(11) default NULL,
-> c2 varchar(30) default NULL,
-> c3 date default NULL) engine=myisam;
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> delimiter //
mysql> CREATE PROCEDURE load_no_part_tab()
-> begin
-> declare v int default 0;
-> while v < 8000000
-> do
-> insert into no_part_tab
-> values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod
3652));
-> set v = v + 1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call load_no_part_tab();
Query OK, 1 row affected (5 min 6.92 sec)
mysql> insert into part_tab select * from no_part_tab;
Query OK, 8000000 rows affected (9.96 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
mysql>
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < da
te '1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (3.88 sec)
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < da
te '1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (3.88 sec)
mysql>
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date
'1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (0.43 sec)
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date
'1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (0.43 sec)
mysql>
mysql> select count(*) from no_part_tab where c3 > '1995-01-01' and c3 < '1995-1
2-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (3.91 sec)
mysql> select count(*) from no_part_tab where c3 > '1995-01-01' and c3 < '1995-1
2-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (3.92 sec)
mysql>
mysql> select count(*) from part_tab where c3 > '1995-01-01' and c3 < '1995-12-3
1';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (0.44 sec)
mysql> select count(*) from part_tab where c3 > '1995-01-01' and c3 < '1995-12-3
1';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (0.44 sec)
mysql>
mysql> explain select count(*) from no_part_tab where c3 > date '1995-01-01' and
c3 < date '1995-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: no_part_tab
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8000000
Extra: Using where
1 row in set (0.00 sec)
mysql> explain partitions select count(*) from part_tab where c3 > date '1995-01
-01' and c3 < date '1995-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: part_tab
partitions: p1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 798458
Extra: Using where
1 row in set (0.00 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.5.5-m3-log |
+--------------+
1 row in set (0.00 sec)
mysql>