Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
SHOW  global STATUS LIKE '%tmp%';

set @UID = 132;

set @param2 = 'cde';

set @param3 = 20130331;

set @param4 = 'C';

select  p.id, p.column44, p.column42, ue.column35, u.id, p.column43, p.column45, p.column46, pp.column63, ue.column36 

             from table1 a1 
                join table2 u on  u.id = a1.column11 
                    join table4 p on p.id = u.column23
                    left join table5 lee on lee.column51 = u.column23 
                join table3 ue on ue.uid = u.id  and ue.column32 = @param3
                    join table6 pp on pp.column62 = ue.column33  and  pp.column21 = ue.column21
            where a1.column12 = @UID
               and a1.column13 in ( 'A11', 'A22', 'A33')
               and u.column22 = true
               and ue.column35 >= @param3                            
               and 1 = (CASE 
                            WHEN @param2 is null THEN 1                            
                            WHEN @param2 = 'ABC' THEN 1  
                            WHEN @param2= 'CDE' THEN ue.med_lob_column62 = @param2 AND (@param4 = 'C' OR ue.ue.column33 in ('123', '456'))
                             WHEN @param2= 'DEF' THEN ue.med_lob_column62 = @param2 AND column33 != 'abcde-f'
                            WHEN @param2= 'GHI' THEN ue.med_lob_column62 = @param2
                        END); 

SHOW  global STATUS LIKE '%tmp%';

I measure the amount of global temp tables created before and after executing the above query. Everytime i execute the query i see an increase of around 500+ in 'Created_tmp_tables' status variable value. I'm confused as to why so many temp table are getting created ? or is it normal Mysql behavior. If so why ? The schema of the above tables look like this:

CREATE TABLE `table1` (
  `column11` int(11) NOT NULL,
  `column12` int(11) NOT NULL,
  `column13` enum('A11','A22','A33','A44','A55','A66') NOT NULL DEFAULT 'A11',
  PRIMARY KEY (`column11`,`column12`,`column13`),
  KEY `table11_index1` (`column12`,`column11`),
  KEY `table11_index2` (`column12`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `table2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `column21` int(11) DEFAULT NULL,
  `column22` tinyint(1) DEFAULT '0',
  `column23` int(11) NOT NULL, 
  PRIMARY KEY (`id`),
  KEY `table2_index1` (`column23`)  
) ENGINE=InnoDB AUTO_INCREMENT=1547471 DEFAULT CHARSET=latin1

CREATE TABLE `table3` (
  `uid` int(11) NOT NULL,
  `column32` date NOT NULL,
  `column33` varchar(10) DEFAULT NULL,  
  `column21` int(11) NOT NULL DEFAULT '4',
  `column35` date DEFAULT NULL,
  `column36` char(5) DEFAULT NULL,
  PRIMARY KEY (`column32`,`uid`),
  KEY `table3_fk2` (`med_lob_column62`),
  KEY `table3_fk5` (`column21`,`column33`),  
  KEY `table3_fk3` (`column21`),
  KEY `table3_fk1` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `table4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `column42` char(1) DEFAULT NULL,
  `column43` varchar(50) DEFAULT NULL,
  `column44` date DEFAULT NULL,
  `column45` varchar(100) DEFAULT NULL,
  `column46` varchar(100) DEFAULT NULL,
   PRIMARY KEY (`id`),
  KEY `table4_index1` (`column45`),
  KEY `table4_index2` (`column46`)  
) ENGINE=InnoDB AUTO_INCREMENT=1564327 DEFAULT CHARSET=latin1

CREATE TABLE `table5` (
  `column51` int(11) NOT NULL,
  `column52` date DEFAULT NULL,
  PRIMARY KEY (`column51`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `table6` (
  `column21` int(10) NOT NULL,
  `column62` varchar(10) NOT NULL,
  `column63` varchar(15) DEFAULT NULL, 
  PRIMARY KEY (`column21`,`column62`),
  KEY `table6_fk1` (`column21`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1
share|improve this question
1  
How busy is the server that you're polling global status from? Try checking the session's tmp table creation (remove GLOBAL) to see if it reports the same 300 tmp table created: SHOW STATUS LIKE '%tmp%' and –  Derek Downey Apr 10 '13 at 11:31
    
Yes, you are right. The session 'Created_tmp_tables' stauts variable shows no change before and after the execution if the above query. I'm surprised to see the total load on the database. Any ways thanks for your update –  user22347 Apr 15 '13 at 7:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.