Re: Time performance in query Inner Join

[angel rivero]

Ok Peter:

>>> (1) the results of SHOW CREATE TABLE for all tables involved in the problem
I can create here two tables with a total is over 3.500.000 records?

>>> (2) enough INSERT statements for each table so that a reader can replicate your problem
Too?

I don't have problems, but I think I can create problems to the forum...
I understand me?

Thanks

 SELECT  C.MCR_Cod , C.MCR_Clt , C.MCR_Den

         , SUM(IF(R.Art = '51M' AND R.CDI='P', R.BT*R.sec/60/C.CLIENTIES, 0)) AS '51M'
         , SUM(IF(R.Art NOT IN ('51M','450') AND R.CDI='P', R.BT*R.sec/60/C.CLIENTIES, 0))  AS 'BT'
         , SUM(IF(R.Art = '450' AND R.CDI='P', R.BT*R.sec/60/C.CLIENTIES, 0)) AS 'MT' 
           
         , SUM(IF(R.Art = '51M' AND R.CDI In('P','B'), R.BT/C.CLIENTIES, 0)) AS '51M'
         , SUM(IF(R.Art NOT IN ('51M','450') AND R.CDI In('P','B'), R.BT/C.CLIENTIES, 0)) AS 'BT'
         , SUM(IF(R.Art = '450' AND R.CDI In('P','B'), R.BT/C.CLIENTIES, 0)) AS 'MT'

     FROM  _tbl1 R
INNER JOIN  _tbl2 C ON R.CFT_Cod = C.CFT_Cod

     WHERE  1
      AND  C.MCR_Cod ='DNO'
     GROUP BY MCR_Cod, MCR_Clt, MCR_Den




-- ----------------------------
-- Table structure for `_tbl1`
-- ----------------------------
DROP TABLE IF EXISTS `_tbl1`;
CREATE TABLE `_tbl1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `CFT_Cod` varchar(255) DEFAULT NULL,
  `CDI` varchar(255) DEFAULT NULL,
  `BT` decimal(10,2) DEFAULT NULL,
  `sec` decimal(10,2) DEFAULT NULL,
  `Art` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `CFT_Cod` (`CFT_Cod`),
  KEY `CDI` (`CDI`),
  KEY `BT` (`BT`),
  KEY `sec` (`sec`),
  KEY `Art` (`Art`)
) ENGINE=InnoDB AUTO_INCREMENT=3665958 DEFAULT CHARSET=latin1;


-- ----------------------------
-- Table structure for `_tbl2`
-- ----------------------------
DROP TABLE IF EXISTS `_tbl2`;
CREATE TABLE `_tbl2` (
  `CFT_Cod` varchar(255) DEFAULT NULL,
  `MCR_Cod` varchar(255) DEFAULT NULL,
  `MCR_Clt` int(11) DEFAULT NULL,
  `MCR_Den` varchar(255) DEFAULT NULL,
  `C.CLIENTIES` int(11) DEFAULT NULL,
  `xID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`xID`),
  KEY `CFT_Cod` (`CFT_Cod`),
  KEY `CLIENTIES` (`CLIENTIES`)
) ENGINE=InnoDB AUTO_INCREMENT=9290 DEFAULT CHARSET=latin1;

Edited 3 time(s). Last edit at 07/07/2011 10:55AM by angel rivero.