A Lightweight SQL Database for Cloud Infrastructure and Web Applications

MySQL 5.5 partition pruning

Asked by Steven Tang on 2010-06-10

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
*************************** 1. row ***************************
       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`,`writetime`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
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
                                                 | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+-------+---------------------------------------------------------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | rd |
p2_p2sp0,p2_p2sp1,p2_p2sp2,p2_p2sp3,p2_p2sp4,p2_p2sp5,p2_p2sp6,p2_p2sp7,p2_p2sp8,p2_p2sp9,p2_p2sp10,p2_p2sp11
| 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,p2_p2sp3,p2_p2sp4,p2_p2sp5.

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:
2010-06-11
Last reply:
2010-06-11
Patrick Crews (patrick-crews) said : #1

This is related to MySQL functionality and isn't relevant here. A cursory look makes it seem as if there is a bug, so I would recommend filing one with MySQL - http://bugs.mysql.com/

You might also try the MySQL partitioning forums - http://forums.mysql.com/list.php?106

I hope this helps.

Steven Tang (stang) said : #2

Sorry I do not know it is not relevant to here.

Please close this one.

-----Original Message-----
From: <email address hidden> [mailto:<email address hidden>] On Behalf Of Patrick Crews
Sent: Thursday, June 10, 2010 4:23 PM
To: Steven Tang
Subject: Re: [Question #114221]: MySQL 5.5 partition pruning

Your question #114221 on Drizzle changed:
https://answers.launchpad.net/drizzle/+question/114221

    Status: Open => Answered

Patrick Crews proposed the following answer:
This is related to MySQL functionality and isn't relevant here. A
cursory look makes it seem as if there is a bug, so I would recommend
filing one with MySQL - http://bugs.mysql.com/

You might also try the MySQL partitioning forums -
http://forums.mysql.com/list.php?106

I hope this helps.

--
If this answers your question, please go to the following page to let us
know that it is solved:
https://answers.launchpad.net/drizzle/+question/114221/+confirm?answer_id=0

If you still need help, you can reply to this email or go to the
following page to enter your feedback:
https://answers.launchpad.net/drizzle/+question/114221

You received this question notification because you are a direct
subscriber of the question.

Patrick Crews (patrick-crews) said : #3

marking as answered per the questioner's request

Can you help with this problem?

Provide an answer of your own, or ask Steven Tang for more information if necessary.

To post a message you must log in.