partition full scan

[arun roy manoharan]

Hi,
With the below query which uses joins on 4 tables which includes partition tables as well. When using the join on the partition table it goes and searches on all the partitions which result in to "copying to tmp table" (i suspect so) and delay in output.
given the query,explain plan,profile and show create table. Any help is appreciated

mysql> explain partitions SELECT 'p1' AS dbname, `STH`.`cmpid_id` , HRGv35att4_id AS HRGv4, SUM( SPELLs ) AS tmp_SPELLs, SUM( SPELLLos ) AS tmp_SPELLLos, ( SUM( SPELLLos ) / SUM( SPELLs ) + 0.000000 ) AS tmp_Index, SUM( OPSPELLs ) AS tmp_OPSPELLs, SUM( sdata.PreopLos ) AS tmp_PreopLos FROM ( p1.thy AS TH JOIN STH AS STH ON TH.id = STH.TH_id JOIN p1.skeyAS AS sk1 ON STH.SubSpec_id = sk1.SUBSPECatt4_id AND STH.cmpid_id = sk1.cmpid_id JOIN p1.st5 AS sdata ON sk1.id = sdata.skeyAS_id ) WHERE ( true AND ( `sk1`.`MONTH` BETWEEN '2006-04-01' AND '2010-06-01' ) ) AND ( ( LevelB = 'SUMMARY' ) ) AND ( ( AGE < 61 ) ) AND ( TH.Map_id =99 ) GROUP BY `STH`.`cmpid_id`;
+----+-------------+-------+---------------------------------------------------------------------------------------+------+--------------------------------------+----------------+---------+------------------------------------------------------------------+-------+-----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+---------------------------------------------------------------------------------------+------+--------------------------------------+----------------+---------+------------------------------------------------------------------+-------+-----------------------------------------------------------+
| 1 | SIMPLE | TH | NULL | ref | PRIMARY,S_TH_Unique | S_TH_Unique | 54 | const,const | 1 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | STH | NULL | ref | PRIMARY,P_STH_UNQ | PRIMARY | 3 | p1.TH.id | 58241 | Using index |
| 1 | SIMPLE | sk1 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23 | ref | PRIMARY,P_S_key_Month,P_S_key_cmpid | P_S_key_cmpid | 4 | p1.STH.cmpid_id,p1.STH.SubSpec_id | 124 | Using where |
| 1 | SIMPLE | sdata | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23 | ref | PRIMARY | PRIMARY | 4 | p1.sk1.id | 1 | |
+----+-------------+-------+---------------------------------------------------------------------------------------+------+--------------------------------------+----------------+---------+----------------

mysql> show profile for query 1;
+--------------------------------+-----------+
| Status | Duration |
+--------------------------------+-----------+
| starting | 0.000034 |
| checking query cache for query | 0.000287 |
| Opening tables | 0.000056 |
| System lock | 0.000056 |
| Table lock | 0.000167 |
| init | 0.000159 |
| optimizing | 0.000091 |
| statistics | 0.000683 |
| preparing | 0.000063 |
| Creating tmp table | 0.000083 |
| executing | 0.000007 |
| Copying to tmp table | 67.072742 |
| Sorting result | 0.000038 |
| Sending data | 0.000061 |
| end | 0.000005 |
| removing tmp table | 0.000033 |
| end | 0.000006 |
| query end | 0.000004 |
| freeing items | 0.000050 |
| storing result in query cache | 0.000017 |
| logging slow query | 0.000002 |
| cleaning up | 0.000007 |
+--------------------------------+-----------+
22 rows in set (0.00 sec)

mysql> show create table thy;
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| thy | CREATE TABLE `thy` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`Map_Id` smallint(5) unsigned NOT NULL DEFAULT '0',
`LevelA` varchar(50) COLLATE latin1_general_ci NOT NULL,
`LevelB` varchar(50) COLLATE latin1_general_ci NOT NULL,
`LevelC` varchar(50) COLLATE latin1_general_ci NOT NULL,
`LevelD` varchar(50) COLLATE latin1_general_ci NOT NULL,
`LevelE` varchar(50) COLLATE latin1_general_ci NOT NULL,
`LevelF` varchar(50) COLLATE latin1_general_ci NOT NULL,
`SiteSpec` text COLLATE latin1_general_ci NOT NULL,
`PeerSpec` text COLLATE latin1_general_ci NOT NULL,
`PeerFCE` mediumtext COLLATE latin1_general_ci NOT NULL,
`PeerSPELL` mediumtext COLLATE latin1_general_ci NOT NULL,
`PeerSites` text COLLATE latin1_general_ci NOT NULL,
`PeerOP` mediumtext COLLATE latin1_general_ci NOT NULL,
`OP_PeerSites` text COLLATE latin1_general_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `S_TH_Unique` (`Map_Id`,`LevelB`,`LevelC`,`LevelD`,`LevelE`,`LevelF`)
) ENGINE=InnoDB AUTO_INCREMENT=5024 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table skeyAS;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| skeyAS | CREATE TABLE `skeyAS` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Lookup_id` int(10) unsigned NOT NULL DEFAULT '0',
`cmpid_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`MONTH` date NOT NULL DEFAULT '0000-00-00',
`NumSpells` tinyint(1) unsigned NOT NULL DEFAULT '1',
`SUBSPECatt1_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`SUBSPECatt2_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`SUBSPECatt3_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`SUBSPECatt4_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`ADMTYPE` char(1) COLLATE latin1_general_ci NOT NULL,
`PBCatt1_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`PBCatt2_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`PBCatt3_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`PBCatt4_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`AM` char(2) COLLATE latin1_general_ci NOT NULL,
`IM` char(1) COLLATE latin1_general_ci NOT NULL,
`DM` char(1) COLLATE latin1_general_ci NOT NULL,
`DD` char(2) COLLATE latin1_general_ci NOT NULL,
`SEX` char(1) COLLATE latin1_general_ci NOT NULL,
`AS` char(2) COLLATE latin1_general_ci NOT NULL,
`ETHNIC` char(2) COLLATE latin1_general_ci NOT NULL,
`HRGv35att1_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`HRGv35att2_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`HRGv35att3_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`HRGv35att4_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`HRGV4att1_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`HRGV4att2_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`HRGV4att3_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`HRGV4att4_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`AGE` smallint(6) DEFAULT NULL,
`PSLOS` smallint(6) DEFAULT NULL,
`WLBFLAG` tinyint(1) NOT NULL DEFAULT '0',
`RDNFLAG` tinyint(1) NOT NULL DEFAULT '0',
`DIALYSISFLAG` tinyint(1) NOT NULL DEFAULT '0',
`InitialTariff` mediumint(8) unsigned NOT NULL DEFAULT '0',
`FinalTariff` mediumint(8) unsigned NOT NULL DEFAULT '0',
`LongStayPayment` mediumint(8) unsigned NOT NULL DEFAULT '0',
`TariffExclMFF` mediumint(8) unsigned NOT NULL DEFAULT '0',
`TariffIncMFF` mediumint(8) unsigned NOT NULL DEFAULT '0',
`HRGv4ExcessDays` mediumint(8) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`,`MONTH`),
KEY `P_S_key_Month` (`MONTH`,`cmpid_id`,`SUBSPECatt4_id`),
KEY `Lookup_id` (`Lookup_id`),
KEY `P_S_key_cmpid` (`cmpid_id`,`SUBSPECatt4_id`,`MONTH`)
) ENGINE=MyISAM AUTO_INCREMENT=275353203 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
/*!50100 PARTITION BY HASH ( ( YEAR(`Month`) + MONTH(`Month`)*4 ))
PARTITIONS 24 */ |


mysql> show create table SubSpecAS;
+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SubSpecAS | CREATE TABLE `SubSpecAS` (
`TH_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`cmpid_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`SubSpec_id` smallint(5) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`TH_id`,`cmpid_id`,`SubSpec_id`),
KEY `P_STH_UNQ` (`cmpid_id`,`TH_id`,`SubSpec_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Uses cmpid_id and SubSpec_id to link to key tables' |
+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> show create table st5;
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| st5 | CREATE TABLE `st5` (
`SPELL_key_id` int(10) unsigned NOT NULL,
`Category` char(1) COLLATE latin1_general_ci NOT NULL,
`Spells` tinyint(1) unsigned NOT NULL DEFAULT '1',
`SpellLos` smallint(5) unsigned NOT NULL DEFAULT '0',
`OPSpells` tinyint(1) unsigned NOT NULL DEFAULT '0',
`PreopLos` smallint(5) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`SPELL_key_id`,`Category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
/*!50100 PARTITION BY HASH ( SPELL_key_id DIV 25000)
PARTITIONS 24 */ |