MySQL Forums
Forum List  »  Optimizer & Parser

Index for Query
Posted by: dileep ch
Date: July 17, 2012 12:41AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Index for Query
2899
July 17, 2012 12:41AM
1433
July 17, 2012 10:36PM
1416
July 18, 2012 11:52PM
2097
July 19, 2012 09:49PM


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.