MySQL Forums
Forum List  »  Optimizer & Parser

Re: Index for Query
Posted by: Rick James
Date: July 17, 2012 10:36PM

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`;
event_logs needs
INDEX(event_id, param4, param5) -- in any order.
A hint was "Using intersect(param4,param5)" -- meaning that it used two INDEXes when (possibly) a single, compound, one would work better.
Even better would be
INDEX(event_id, jail_broken, param4, param5, param6, cdate) -- with cdate _last_

> KEY `event_id` (`event_id`,`cdate`,`jail_broken`,`udid`),
If you usually use BETWEEN with `cdate`, put it last. Otherwise, the use of the index will stop with `cdate`, and the subsequent fields will go unused.

event_param_values smells like a EAV design pattern. Performance sucks for such.

> `ip` int(32) NOT NULL,
If that is an IP address, keep in mind that IPv6 is upon us. It needs 128 bits, and won't fit in an INT.

Consider establishing "summary tables".

You are likely to have grief over splitting cdate and ctime into two fields.

Many of the topics I mention have been discussed, in more detail, repeatedly in this forum, the Performance forum and the Newbie forum.

Options: ReplyQuote

Written By
July 17, 2012 12:41AM
Re: Index for Query
July 17, 2012 10:36PM
July 18, 2012 11:52PM
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.