Re: After partition also select is scanning all the partitions - need help
Posted by:
Venkat V
Date: September 16, 2010 07:06PM
Here is the create statement.
mysql> show create table mytable \G
*************************** 1. row ***************************
Table: mytable
Create Table: CREATE TABLE `mytable ` (
`HostId` int(15) unsigned NOT NULL,
`JobId` int(15) unsigned NOT NULL,
`ChangeId` int(30) unsigned NOT NULL AUTO_INCREMENT,
`Date` date NOT NULL DEFAULT '0000-00-00',
`CPUUtil` int(20) NOT NULL DEFAULT '0',
`DiskUtil` int(20) NOT NULL DEFAULT '0',
`LoadAvg` int(20) NOT NULL DEFAULT '0',
`Uptime` float(15,2) NOT NULL DEFAULT '0.00',
`Threshold` blob,
`UpdatedTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ChangeId`,`Date`),
KEY `HostId` (`HostId`),
KEY `ColoId` (`ColoId`),
KEY `date_idx` (`Date`)
) ENGINE=MyISAM AUTO_INCREMENT=6552 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50100 PARTITION BY RANGE (to_days(Date))
(PARTITION PART_20100801000000 VALUES LESS THAN (734348) ENGINE = MyISAM,
PARTITION PART_20100818000000 VALUES LESS THAN (734368) ENGINE = MyISAM,
PARTITION PART_20100819000000 VALUES LESS THAN (734369) ENGINE = MyISAM,
PARTITION PART_20100820000000 VALUES LESS THAN (734370) ENGINE = MyISAM,
PARTITION PART_20100821000000 VALUES LESS THAN (734371) ENGINE = MyISAM,
PARTITION PART_20100822000000 VALUES LESS THAN (734372) ENGINE = MyISAM,
PARTITION PART_20100823000000 VALUES LESS THAN (734373) ENGINE = MyISAM,
PARTITION PART_20100824000000 VALUES LESS THAN (734374) ENGINE = MyISAM,
PARTITION PART_20100825000000 VALUES LESS THAN (734375) ENGINE = MyISAM,
PARTITION PART_20100826000000 VALUES LESS THAN (734376) ENGINE = MyISAM,
PARTITION PART_20100827000000 VALUES LESS THAN (734377) ENGINE = MyISAM,
PARTITION PART_20100828000000 VALUES LESS THAN (734378) ENGINE = MyISAM,
PARTITION PART_20100829000000 VALUES LESS THAN (734379) ENGINE = MyISAM,
PARTITION PART_20100830000000 VALUES LESS THAN (734380) ENGINE = MyISAM,
PARTITION PART_20100831000000 VALUES LESS THAN (734381) ENGINE = MyISAM,
PARTITION PART_20100901000000 VALUES LESS THAN (734382) ENGINE = MyISAM,
PARTITION PART_20100902000000 VALUES LESS THAN (734383) ENGINE = MyISAM,
PARTITION PART_20100903000000 VALUES LESS THAN (734384) ENGINE = MyISAM,
PARTITION PART_20100904000000 VALUES LESS THAN (734385) ENGINE = MyISAM,
PARTITION PART_20100905000000 VALUES LESS THAN (734386) ENGINE = MyISAM,
PARTITION PART_20100906000000 VALUES LESS THAN (734387) ENGINE = MyISAM,
PARTITION PART_20100907000000 VALUES LESS THAN (734388) ENGINE = MyISAM,
PARTITION PART_20100908000000 VALUES LESS THAN (734389) ENGINE = MyISAM,
PARTITION PART_20100909000000 VALUES LESS THAN (734390) ENGINE = MyISAM,
PARTITION PART_20100910000000 VALUES LESS THAN (734391) ENGINE = MyISAM,
PARTITION PART_20100911000000 VALUES LESS THAN (734392) ENGINE = MyISAM,
PARTITION PART_20100912000000 VALUES LESS THAN (734393) ENGINE = MyISAM,
PARTITION PART_20100913000000 VALUES LESS THAN (734394) ENGINE = MyISAM,
PARTITION PART_20100914000000 VALUES LESS THAN (734395) ENGINE = MyISAM,
PARTITION PART_20100915000000 VALUES LESS THAN (734396) ENGINE = MyISAM,
PARTITION PART_20100916000000 VALUES LESS THAN (734397) ENGINE = MyISAM,
PARTITION PART_20100917000000 VALUES LESS THAN (734398) ENGINE = MyISAM,
PARTITION PART_20100918000000 VALUES LESS THAN (734399) ENGINE = MyISAM,
PARTITION PART_20100919000000 VALUES LESS THAN (734400) ENGINE = MyISAM,
PARTITION PART_20100920000000 VALUES LESS THAN (734401) ENGINE = MyISAM,
PARTITION PART_20100921000000 VALUES LESS THAN (734402) ENGINE = MyISAM,
PARTITION PART_20100922000000 VALUES LESS THAN (734403) ENGINE = MyISAM,
PARTITION PART_20100923000000 VALUES LESS THAN (734404) ENGINE = MyISAM,
PARTITION PART_20100924000000 VALUES LESS THAN (734405) ENGINE = MyISAM,
PARTITION PART_20100925000000 VALUES LESS THAN (734406) ENGINE = MyISAM,
PARTITION PART_20100926000000 VALUES LESS THAN (734407) ENGINE = MyISAM,
PARTITION PART_20100927000000 VALUES LESS THAN (734408) ENGINE = MyISAM,
PARTITION PART_20100928000000 VALUES LESS THAN (734409) ENGINE = MyISAM,
PARTITION PART_20100929000000 VALUES LESS THAN (734410) ENGINE = MyISAM,
PARTITION PART_20100930000000 VALUES LESS THAN (734411) ENGINE = MyISAM) */
1 row in set (0.00 sec)
mysql>