Re: partition table performance

[fz wsc]

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,