MySQL Forums
Forum List  »  Partitioning

partition full scan
Posted by: arun roy manoharan
Date: December 13, 2010 06:55AM

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 */ |

Options: ReplyQuote


Subject
Views
Written By
Posted
partition full scan
2425
December 13, 2010 06:55AM
1500
December 14, 2010 04:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.