Re: MySQL Partitioning showing low performance

[vo ko]

usage: only select, one time a day added new table/partition, no update queries. Selects are filtered by different columns
tables structures

CREATE TABLE IF NOT EXISTS `datatable_20121019` (
`col1` int(11) unsigned NOT NULL,
`col2` int(11) unsigned NOT NULL,
`col3` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`col4` float NOT NULL,
`col5` float NOT NULL,
`col6` float NOT NULL,
`col7` float NOT NULL,
`col8` float NOT NULL,
`col9` float NOT NULL,
`col10` date NOT NULL,
`col13` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`col14` float NOT NULL,
`col11` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`col12` float NOT NULL,
`col15` float NOT NULL,
`col16` int(11) unsigned NOT NULL,
`col17` float NOT NULL,
`col18` float NOT NULL,
KEY `key2` (`col10`,`col3`,`col11`,`col13`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE IF NOT EXISTS `datatable3` (
`dt` date NOT NULL,
`col1` int(11) unsigned NOT NULL,
`col2` int(11) unsigned NOT NULL,
`col3` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`col4` float NOT NULL,
`col5` float NOT NULL,
`col6` float NOT NULL,
`col7` float NOT NULL,
`col8` float NOT NULL,
`col9` float NOT NULL,
`col10` date NOT NULL,
`col11` float NOT NULL,
`col12` float NOT NULL,
`col13` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`col14` float NOT NULL,
`col15` float NOT NULL,
`col16` int(11) unsigned NOT NULL,
`col17` float NOT NULL,
`col18` float NOT NULL,
KEY `key1` (`col3`,`dt`),
KEY `key2` (`col10`,`col3`,`col11`,`col13`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50500 PARTITION BY RANGE COLUMNS(dt)
(PARTITION p20120402 VALUES LESS THAN ('2012-04-03') ENGINE = MyISAM,
PARTITION p20120403 VALUES LESS THAN ('2012-04-04') ENGINE = MyISAM,
PARTITION p20120404 VALUES LESS THAN ('2012-04-05') ENGINE = MyISAM,
PARTITION p20120405 VALUES LESS THAN ('2012-04-06') ENGINE = MyISAM,
PARTITION p20120409 VALUES LESS THAN ('2012-04-10') ENGINE = MyISAM,
PARTITION p20120410 VALUES LESS THAN ('2012-04-11') ENGINE = MyISAM,
....
*/

also I tested with PARTITION BY LIST, it has the same spead
/*!50500 PARTITION BY LIST COLUMNS(dt)
(PARTITION p20120402 VALUES IN ('2012-04-02') ENGINE = MyISAM,
PARTITION p20120403 VALUES IN ('2012-04-03') ENGINE = MyISAM,
PARTITION p20120404 VALUES IN ('2012-04-04') ENGINE = MyISAM,
PARTITION p20120405 VALUES IN ('2012-04-05') ENGINE = MyISAM,
PARTITION p20120409 VALUES IN ('2012-04-09') ENGINE = MyISAM,
PARTITION p20120410 VALUES IN ('2012-04-10') ENGINE = MyISAM,
PARTITION p20120411 VALUES IN ('2012-04-11') ENGINE = MyISAM,
....
*/

Query
107.17ms SELECT COUNT(1) AS `zend_paginator_row_count` FROM `datatable3` WHERE (dt = '20121019') AND (col11 > 0 AND col13 > 0)
0.12ms SELECT COUNT(1) AS `zend_paginator_row_count` FROM `datatable_20121019` WHERE (col11 > 0 AND col13 > 0)


I tested datatable3 with MyISAM and InnoDB engine (mysql 5.5), InnoDB is faster per 10% (first query myisam makes faster 1.5 times), but need more space than myisam (about 150%)
SHOW TABLE STATUS:
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
datatable3 InnoDB 10 Compact 22472690 142 3192504320 0 1281785856 704643072 NULL NULL NULL NULL utf8_unicode_ci NULL partitioned
datatable_20130202 MyISAM 10 Dynamic 194912 89 17538684 281474976710655 4972544 0 NULL 2012-09-18 21:19:49 2013-02-12 18:58:07 NULL utf8_unicode_ci NULL


EXPLAIN

EXPLAIN PARTITIONS SELECT COUNT( 1 ) AS `zend_paginator_row_count`
FROM `datatable3`
WHERE (
dt = '20121019'
)
AND (
col11 >0
AND col13 >0
)
AND (
col5 >= 0.15
)
AND (
col10 = '2012-10-26'
)
AND (
col9 *100 <= '1000'
)
AND (
col6 >=0
)
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE datatable3 p20121019 ref key2 key2 3 const 3424 Using where


EXPLAIN SELECT COUNT( 1 ) AS `zend_paginator_row_count`
FROM `datatable_20121019`
WHERE (
col11 >0
AND col13 >0
)
AND (
col5 >= 0.15
)
AND (
col10 = '2012-10-26'
)
AND (
col9 *100 <= '1000'
)
AND (
col6 >=0
)
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE datatable_20121019 ref key2 key2 3 const 4124 Using where