Re: Preformance in multiple join

[Rasmus Toftegaard]

Hi

My problem is a part of a big syntax. I have now reduced my problem to the two tables. My SQL:

SELECT data_id, Count(data_id) AS calls, SUM(duration / 60) AS duration, AVG(duration / 60) AS avg_duration, Sum(NVO.p_minute*duration/60) as novaoutmin, Sum(NR.p_minute*duration/60) as novaRoutingMin, Sum(CO.p_minute*duration/60) as clientOutMin, Sum(CR.p_minute*duration/60) as clientRoutingMin, Sum(NVO.p_dial) as novaOutDial,Sum(CO.p_dial) as clientOutDial, NVO.currency as currency FROM t_telco_data D
LEFT JOIN
(SELECT * FROM t_price P WHERE price_type='novaout') NVO ON NVO.dial_string=D.dial_String AND FIND_IN_SET(D.billing_type,NVO.billing_type)>0 AND ( (D.date BETWEEN NVO.start_date AND NVO.end_date)) LEFT JOIN
(SELECT * FROM t_price P WHERE price_type='clientout') CO ON CO.dial_string=D.dial_String AND FIND_IN_SET(D.billing_type,CO.billing_type)>0 AND ( (D.date BETWEEN CO.start_date AND CO.end_date)) LEFT JOIN
(SELECT * FROM t_price P WHERE price_type='novarouting') NR ON NR.dial_string=D.dial_String AND FIND_IN_SET(D.billing_type,NR.billing_type)>0 AND ( (D.date BETWEEN NR.start_date AND NR.end_date)) LEFT JOIN
(SELECT * FROM t_price P WHERE price_type='clientrouting') CR ON CR.dial_string=D.dial_String AND FIND_IN_SET(D.billing_type,CR.billing_type)>0 AND ( (D.date BETWEEN CR.start_date AND CR.end_date))
WHERE D.date BETWEEN '2006-12-01' AND '2006-12-31' GROUP BY NVO.currency ORDER BY date ASC;


The table structures is here:

/*Table structure for table `t_price` */

CREATE TABLE `t_price` (
`price_id` int(5) NOT NULL auto_increment,
`template_id` int(3) NOT NULL default '0',
`p_minute` double default NULL,
`p_dial` double default NULL,
`dial_string` varchar(15) default NULL,
`currency` int(3) default NULL,
`start_date` datetime default NULL,
`end_date` datetime default NULL,
`price_type` varchar(30) default NULL,
`billing_type` varchar(30) default 'plain',
PRIMARY KEY (`price_id`),
KEY `dial_string` (`dial_string`),
KEY `start_date` (`start_date`),
KEY `end_date` (`end_date`),
KEY `template_id` (`template_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `t_telco_data` */

CREATE TABLE `t_telco_data` (
`data_id` int(10) unsigned NOT NULL auto_increment,
`date` date NOT NULL default '0000-00-00',
`start_time` time NOT NULL default '00:00:00',
`dial_string` varchar(15) NOT NULL default '',
`cli` varchar(15) NOT NULL default '',
`duration` int(6) NOT NULL default '0',
`log_id` int(10) unsigned default '0',
`isExport` char(1) default '0',
`billing_type` varchar(30) default 'plain',
PRIMARY KEY (`data_id`),
KEY `date` (`date`),
KEY `dial_string` (`dial_string`),
KEY `isExport` (`isExport`),
KEY `billing_type` (`billing_type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;