MySQL Forums
Forum List  »  Partitioning

Re: Diffrent storage engines on partitions in the same table
Posted by: Phil Hildebrand
Date: April 14, 2009 08:31AM

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 | |
+----+--------------+------------+-----------------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Diffrent storage engines on partitions in the same table
2409
April 14, 2009 08:31AM


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.