MySQL 5.5 partition pruning
Well for the new MySQL 5.5.3-m3 server, I have following results that one is expected and
the other is not expected.
-------
mysql> show create table rd\G
*******
Table: rd
Create Table: CREATE TABLE `rd` (
`full_name` varchar(60) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column
full_name is the street name of the road',
`l_f_add` int(11) DEFAULT NULL COMMENT 'Column l_f_add left from address, carried from
ESRI shp spec',
`l_t_add` int(11) DEFAULT NULL COMMENT 'Column l_t_add is left to address, carried from
ESRI shp spec',
`r_f_add` int(11) DEFAULT NULL COMMENT 'Column r_f_add is right from address, carried
from ESRI shp spec',
`r_t_add` int(11) DEFAULT NULL COMMENT 'Column r_t_add is right to address, carried
from ESRI shp spec',
`cityl` varchar(60) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column cityl is
city name',
`zipl` varchar(10) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column zipl is zip
code to the left, carried from ESRI shp spec',
`zipr` varchar(10) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column zipr is zip
code to the right, carried from ESRI shp spec',
`state_abbr` varchar(32) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column
state_abbr is the abbreviation of the state or province the road located',
`shapeid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Column shapeid is a sequence
number generated by database as primary key (PK) for each record',
`speedkm` int(11) DEFAULT NULL COMMENT 'Column speedkm is the speed limit in KM of the
road',
`speedtype` char(1) COLLATE latin1_general_ci NOT NULL DEFAULT 'N' COMMENT 'Column
speedtype is used to indicate the speed limit type. P: posted; C: category; N:
not-in-use',
`writetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Column writetime
is the insertion time stamp of the record',
PRIMARY KEY (`shapeid`
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 COLLATE=
COMMENT='See schema_dictionary table for details'
/*!50100 PARTITION BY RANGE (year (writetime))
SUBPARTITION BY HASH (month(writetime))
SUBPARTITIONS 12
(PARTITION p0 VALUES LESS THAN (2008) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2009) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2011) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (2012) ENGINE = InnoDB,
PARTITION pM VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
mysql> explain partitions select * from rd where writetime = '2009-12-01';
+----+-
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-
| 1 | SIMPLE | rd | p2_p2sp0 | ALL | NULL | NULL | NULL | NULL |
77 | Using where |
+----+-
1 row in set (0.00 sec)
mysql> explain partitions select * from rd where writetime >= '2009-02-01' and writetime
<= '2009-05-28';
+----+-
| id | select_type | table | partitions
ref | rows | Extra |
+----+-
| 1 | SIMPLE | rd |
p2_p2sp0,
| ALL | NULL | NULL | NULL | NULL | 77 | Using where |
+----+-
1 row in set (0.00 sec)
The first explain gives the correct partition p2_p2sp0, but the second one gives the
wrong partitions (it s the whole 12 partitions of the 2009 year), the correct result
should be p2_p2sp2,
Could you please explain why?
Thanks,
Steven
Question information
- Language:
- English Edit question
- Status:
- Answered
- For:
- Drizzle Edit question
- Assignee:
- No assignee Edit question
- Last query:
- Last reply:
Can you help with this problem?
Provide an answer of your own, or ask Steven Tang for more information if necessary.