MySQL Forums
Forum List  »  Partitioning

Re: partition table performance
Posted by: fz wsc
Date: July 25, 2006 03:00AM

Hi, Mikael,

Summary:

CREATE TABLE `table1` (
`ID` int(11) DEFAULT NULL,
`I1` int(11) DEFAULT NULL,
`I2` int(11) DEFAULT NULL,
`I3` int(11) DEFAULT NULL,
`V1` varchar(50) DEFAULT NULL,
`V2` varchar(50) DEFAULT NULL,
`V3` varchar(50) DEFAULT NULL,
`V4` varchar(50) DEFAULT NULL,
`V5` varchar(100) DEFAULT NULL,
`V6` varchar(100) DEFAULT NULL
`Da1` date DEFAULT NULL,
`De1` decimal(18,4) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE (I1) (PARTITION p0 VALUES LESS THAN ..........);

table1 has a column `ID` and should be used as primary key, but for mysql's partition table, the `partitioned by` column has to be included in `primary key`, so to keep my test simple, I just use no primary key at the beginning of my test.

I tested my case in both mysql and sqlserver2005 and found that, partitioned by key(I1), is the best choice for me. In sql server 2005, I can get the best performance for:

1) delete from table1 where I1=12345;
-- note: about 50 thousand rows deleted.

2) insert into table1 (select sequenceForID(), 12345, I2, I3, V1, V2, V3, V4, V5, V6, Da1, De1 from table2);
-- note: table2 is just a temp table with 50 thousand rows, I just copy these rows into table1(I1=12345).

3) select * from table1 where V1 like 'abcd%' order by V1,I2 limit 1,2000;
-- I use clusted index in sql server 2005 to make this `select ...` fast:
CREATE CLUSTERED INDEX [V1I2] ON [table1]([V1],[I2])

This case can be described as: a) delete_old&insert_new books by the publisher ID frequently; b) most of the user will search the books by its title, the result will be sorted by title_of_books and level_of_publishers;

Now, in mysql 5.1.11, if I partition table1 by I1, the 1) and 2) will be almost as fast as sql server 2005,
but the 3) is much slower than sqlserver2005. I've tried several different partition schema in mysql and found
not acceptable solution.

I'd like to help test your new version. :)

Regards,

Options: ReplyQuote


Subject
Views
Written By
Posted
6959
July 14, 2006 12:06AM
3180
July 18, 2006 10:57PM
3320
July 19, 2006 03:00AM
Re: partition table performance
4916
July 25, 2006 03:00AM


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.