MySQL Forums
Forum List  »  Data Warehouse

Re: Optimizing it even more
Posted by: Rick James
Date: May 31, 2011 08:53PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
6197
May 27, 2011 06:14AM
3034
May 28, 2011 10:49PM
2563
May 30, 2011 08:57AM
2852
May 30, 2011 07:15PM
2898
May 31, 2011 10:09AM
Re: Optimizing it even more
2857
May 31, 2011 08:53PM
2922
June 01, 2011 08:42AM
3099
June 01, 2011 09:15AM
2953
June 02, 2011 02:13AM


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.