Index for Query

[dileep ch]

Hi,
SELECT SUM(b.`value`) AS cnt, COUNT(*) AS eCnt, a.`param6`, a.`cdate` AS timeline, COUNT(DISTINCT(a.`udid`)) AS userCnt FROM `event_logs` AS a JOIN `segment_users` AS c ON a.`udid` = c.`udid` JOIN `event_param_values` AS b ON a.`param2` = b.`id` WHERE a.`event_id` = 3 AND c.segment_id = 23 AND a.`cdate` BETWEEN '2012-06-27' AND '2012-07-03' AND a.`param4` = 20 AND a.`param5` = 21 AND a.`jail_broken` = 0 GROUP BY a.`param6`, a.`cdate`;

This is the query executing more than 2 min
and we have a table structure like
CREATE TABLE `event_logs_new` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`event_id` int(11) NOT NULL,
`session_id` bigint(20) NOT NULL,
`param1` int(11) NOT NULL,
`param2` int(11) NOT NULL,
`param3` int(11) NOT NULL,
`param4` int(11) NOT NULL,
`param5` int(11) NOT NULL,
`param6` int(11) NOT NULL,
`param7` int(11) NOT NULL,
`param8` int(11) NOT NULL,
`lat` float NOT NULL DEFAULT '0',
`longi` float NOT NULL DEFAULT '0',
`country_id` int(11) NOT NULL,
`ip` int(32) NOT NULL,
`cdate` date NOT NULL,
`ctime` time NOT NULL,
`cts` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`os_version` int(11) NOT NULL,
`app_version` int(11) NOT NULL,
`device_version` int(11) NOT NULL,
`udid` int(11) NOT NULL,
`jail_broken` tinyint(4) NOT NULL DEFAULT '0',
`jail_broken2` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `param1` (`param1`),
KEY `param2` (`param2`),
KEY `param3` (`param3`),
KEY `param4` (`param4`),
KEY `param5` (`param5`),
KEY `param6` (`param6`),
KEY `param7` (`param7`),
KEY `param8` (`param8`),
KEY `event_id` (`event_id`,`cdate`,`jail_broken`,`udid`),
KEY `event_id_3` (`event_id`,`country_id`,`cdate`,`jail_broken`,`udid`),
KEY `event_id_4` (`event_id`,`cdate`,`jail_broken`,`country_id`)
) ENGINE=InnoDB


event_pram_values

CREATE TABLE `event_param_values` (
`id` bigint(22) NOT NULL AUTO_INCREMENT,
`param_id` int(11) NOT NULL,
`value` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
and

segment_users
CREATE TABLE `segment_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`segment_id` int(11) NOT NULL DEFAULT '0',
`udid` int(11) NOT NULL DEFAULT '0',
`cdate` date NOT NULL DEFAULT '0000-00-00',
PRIMARY KEY (`id`),
KEY `segment_id` (`segment_id`),
KEY `udid` (`udid`)
) ENGINE=InnoDB

which index i have to add for quick result
and eplain of this query is

+----+-------------+-------+-------------+-----------------------------------------------------+---------------+---------+--------------------------+------+-------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+-----------------------------------------------------+---------------+---------+--------------------------+------+-------------------------------------------------------------+
| 1 | SIMPLE | a | index_merge | param2,param4,param5,event_id,event_id_3,event_id_4 | param4,param5 | 4,4 | NULL | 1546 | Using intersect(param4,param5); Using where; Using filesort |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 8 | fairytale_index.a.param2 | 1 | Using where |
| 1 | SIMPLE | c | ref | segment_id,udid | udid | 4 | fairytale_index.a.udid | 1 | Using where |
+----+-------------+-------+-------------+-----------------------------------------------------+---------------+---------+--------------------------+------+-------------------------------------------------------------+
3 rows in set (0.31 sec)


Thanks in advance.