Re: Diffrent storage engines on partitions in the same table

[Phil Hildebrand]

I don't believe that's possible yet.

Depending on your typical access, you could use a view...

Use MyIsam to partition your most current partitions used in queries, and then create a separate table that's partitioned with the archive engine.

Create a view that unions the two tables together

Create view big_table as select * from big_part_table_myisam union big_part_table_archive.

Then for maint you would have to run bulk inserts / selects from the last partition of the myisam table into the first / new partition of the archive table each time you wanted to 'archive' an old partition from the myisam table.

you would want to test how well the optimizer prunes partitions through accessing the view... (from my tests with 5.1.29-rc it appears that it's not able to)

mysql> explain partitions select * from test2 where id =2 and mydate = '2009-04-14 07:22:45';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test2 | p18 | ALL | NULL | NULL | NULL | NULL | 20 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain partitions select * from test_table where id =2 and mydate = '2009-04-14 07:22:45';
+----+--------------+------------+-----------------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-----------------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
| 2 | DERIVED | test | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 3 | UNION | test2 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19 | ALL | NULL | NULL | NULL | NULL | 20 | |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+-----------------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+