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.