MySQL Forums
Forum List  »  Data Warehouse

Re: Optimizing it even more
Posted by: Rick James
Date: May 30, 2011 07:15PM

Is this the real query?
SELECT DISTINCT a.* FROM d_agents a INNER JOIN f_sessions s ON...
How many rows does it return?

d_agents has about 1.2M rows; how many DISTINCT rows?
SELECT DISTINCT * FROM d.agents;

This starts at the beginning of a month, as do the partitions. Is that always the case?
AND s.begin_timestamp >= '2010-01-01 00:00:00'
AND s.begin_timestamp <= '2011-05-25 23:59:59'

Please provide the results of this:
SELECT  COUNT(*),
        COUNT(DISTINCT s.agent_fk)
    FROM  f_sessions s ON a.agent_id = s.agent_fk
      AND  s.begin_timestamp >=          '2011-04-01'
      AND  s.begin_timestamp <  DATE_ADD('2011-04-01', INTERVAL 1 MONTH)
      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);
I'm looking for the feasibility of collecting agent_ids in a subquery, then reaching for a.*.

Then, I will consider the feasibility of having a summary table, by day:
(day, bot_fk, agent_fk). This would be much more efficient to then join to (or use as a subquery with) d.agents.

Options: ReplyQuote


Subject
Views
Written By
Posted
6203
May 27, 2011 06:14AM
3036
May 28, 2011 10:49PM
2566
May 30, 2011 08:57AM
Re: Optimizing it even more
2853
May 30, 2011 07:15PM
2898
May 31, 2011 10:09AM
2861
May 31, 2011 08:53PM
2924
June 01, 2011 08:42AM
3099
June 01, 2011 09:15AM
2955
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.