Please compare the execution times and EXPLAIN plans for the original
EXPLAIN
SELECT DISTINCT a.*
FROM d_agents a
INNER JOIN f_sessions s ON a.agent_id = s.agent_fk
AND s.begin_timestamp >= '2010-01-01 00:00:00'
AND s.begin_timestamp <= '2011-05-25 23:59:59'
AND s.bot_fk IN (1,2,3,4,5,6,7,8,9,
10,11,12,13,14,15,16,17,18,19,20,21,
22,23,24,25,26,27,28,29,30,31,32);
and this modification:
EXPLAIN
SELECT a.*
FROM d_agents a
JOIN ( SELECT DISTINCT s.agent_fk
FROM f_sessions s
AND s.begin_timestamp >= '2010-01-01 00:00:00'
AND s.begin_timestamp <= '2011-05-25 23:59:59'
AND s.bot_fk IN (1,2,3,4,5,6,7,8,9,
10,11,12,13,14,15,16,17,18,19,20,21,
22,23,24,25,26,27,28,29,30,31,32) z
ON a.agent_id = z.agent_fk ;
The idea is to finish with the DISTINCT while working with `s`, then reach into `a` many fewer times.
If that works, then I would recommend a daily job to collect summary information for the previous day. And put the output into a permanent table. This way you are not scanning your huge table repeatedly.
SELECT DISTINCT TO_DATE(dy) as dy, agent_fk, bot_fk
FROM f_sessions s
WHERE s.begin_timestamp >= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
AND s.begin_timestamp < CURRENT_DATE
It would have PRIMARY KEY(dy, bot_fk, agent_fk)
Then the 'report' would run agains the table that this is put into. And it can deal with months, years, whatever.