Re: Some way of doing partitioning before 5.1?
I don't think there is any way before 5.1 to do partitioning, out side of the 'Old School' method.
Something like creating multiple tables, and insert/delete/update trigger, and a union view. This would get you some advantages on maint and fragmentation (possibly I/O), but the optimizer wouldn't understand the partitions. (you'd have to make your code aware of the partitions).
With the example below, you could rebuild just one table (partition) without affecting other table/partitions of the table, and inserts/deletes would only fragment the given tables/partitions, but seeks/scans would still hit all tables/parititons.
Oh... and it gives a good reason to use the blackhole engine :)
Example:
CREATE TABLE test
(
id int(11) NOT NULL DEFAULT '0',
my_name varchar(20) DEFAULT NULL,
PRIMARY KEY ("id")
) engine=blackhole;
CREATE TABLE test_part_part_one
(
id int(11) NOT NULL DEFAULT '0',
my_name varchar(20) DEFAULT NULL,
PRIMARY KEY ("id")
) engine=innodb;
CREATE TABLE test_part_over_10
(
id int(11) NOT NULL DEFAULT '0',
my_name varchar(20) DEFAULT NULL,
PRIMARY KEY ("id")
) engine=innodb;
CREATE TABLE test_part_over_20
(
id int(11) NOT NULL DEFAULT '0',
my_name varchar(20) DEFAULT NULL,
PRIMARY KEY ("id")
) engine=innodb;
CREATE TABLE test_part_over_40
(
id int(11) NOT NULL DEFAULT '0',
my_name varchar(20) DEFAULT NULL,
PRIMARY KEY ("id")
) engine=innodb;
Create a trigger for partitioning:
DELIMITER /
CREATE TRIGGER test_part BEFORE INSERT ON test
FOR EACH ROW BEGIN
if new.id > 40 then
INSERT INTO test_part_over_40 values (new.id,new.my_name);
elseif new.id > 20 then
INSERT INTO test_part_over_20 values (new.id,new.my_name);
elseif new.id > 10 then
INSERT INTO test_part_over_10 values (new.id,new.my_name);
else
INSERT INTO test_part_one values (new.id,new.my_name);
end if;
END;
/
DELIMITER ;
Create a view that unions the tables (partitions):
CREATE VIEW test_all AS
select * from test_part_one
union all
select * from test_part_over_10
union all
select * from test_part_over_20
union all
select * from test_part_over_40
;
Insert some data into test:
mysql> insert into test values (1,'Phil 1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values (8,'Phil 8');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values (28,'Phil 28');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values (58,'Phil 58');
Query OK, 1 row affected (0.00 sec)
Verify test table with blackhole engine shows no data:
mysql> select * from test;
Empty set (0.00 sec)
Select from the view:
mysql> select * from test_all;
+----+---------+
| id | my_name |
+----+---------+
| 1 | Phil 1 |
| 8 | Phil 8 |
| 28 | Phil 28 |
| 58 | Phil 58 |
+----+---------+
4 rows in set (0.00 sec)
mysql> select * from test_all where id = 28;
+----+---------+
| id | my_name |
+----+---------+
| 28 | Phil 28 |
+----+---------+
1 row in set (0.00 sec)
However, the optimizer doesn't know:
mysql> explain select * from test_all where id = 28;
+----+--------------+-------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-------------------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
| 2 | DERIVED | test_part_one | ALL | NULL | NULL | NULL | NULL | 2 | |
| 3 | UNION | test_part_over_10 | ALL | NULL | NULL | NULL | NULL | 1 | |
| 4 | UNION | test_part_over_20 | ALL | NULL | NULL | NULL | NULL | 1 | |
| 5 | UNION | test_part_over_40 | ALL | NULL | NULL | NULL | NULL | 1 | |
| NULL | UNION RESULT | <union2,3,4,5> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+-------------------+------+---------------+------+---------+------+------+-------------+