> show create table cc_agents_tier_status_log: CREATE TABLE "cc_agents_tier_status_log" ( "id" int(10) unsigned NOT NULL AUTO_INCREMENT, "date_log" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, "cc_agent" varchar(45) NOT NULL, "cc_agent_tier_status_id" tinyint(3) unsigned NOT NULL, "cc_queue_id" tinyint(3) unsigned NOT NULL, "cc_agent_id" int(10) unsigned NOT NULL, "cc_agent_phone" smallint(5) unsigned NOT NULL, "cc_agent_domain" varchar(45) NOT NULL DEFAULT 'pbx01.apa-canal.md', PRIMARY KEY ("id"), KEY "IDX_cc_agents_tier_status_log_2" ("cc_agent") USING HASH, KEY "IDX_cc_agents_tier_status_log_3" ("date_log"), KEY "FK_cc_agents_tier_status_log_2" ("cc_agent_id"), KEY "FK_cc_agents_tier_status_log_3" ("cc_queue_id"), KEY "FK_cc_agents_tier_status_log_1" ("cc_agent_tier_status_id") USING BTREE, KEY "IDX_cc_agents_tier_status_log_7" ("id","date_log"), CONSTRAINT "FK_cc_agents_tier_status_log_1" FOREIGN KEY ("cc_agent_tier_status_id") REFERENCES "cc_agent_tier_status_chart" ("id") ON UPDATE CASCADE, CONSTRAINT "FK_cc_agents_tier_status_log_2" FOREIGN KEY ("cc_agent_id") REFERENCES "apacanal"."employee" ("id") ON UPDATE CASCADE, CONSTRAINT "FK_cc_agents_tier_status_log_3" FOREIGN KEY ("cc_queue_id") REFERENCES "cc_queues" ("id") ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=23799 DEFAULT CHARSET=ascii
> show index from cc_agents_tier_status_log: Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment cc_agents_tier_status_log 0 PRIMARY 1 id A 23999 (null) BTREE (null) (null) cc_agents_tier_status_log 1 IDX_cc_agents_tier_status_log_2 1 cc_agent A 260 (null) BTREE (null) (null) cc_agents_tier_status_log 1 IDX_cc_agents_tier_status_log_3 1 date_log A 23999 (null) BTREE (null) (null) cc_agents_tier_status_log 1 FK_cc_agents_tier_status_log_2 1 cc_agent_id A 2 (null) BTREE (null) (null) cc_agents_tier_status_log 1 FK_cc_agents_tier_status_log_3 1 cc_queue_id A 14 (null) BTREE (null) (null) cc_agents_tier_status_log 1 FK_cc_agents_tier_status_log_1 1 cc_agent_tier_status_id A 2 (null) BTREE (null) (null) cc_agents_tier_status_log 1 IDX_cc_agents_tier_status_log_7 1 id A 23999 (null) BTREE (null) (null) cc_agents_tier_status_log 1 IDX_cc_agents_tier_status_log_7 2 date_log A 23999 (null) BTREE (null) (null)
And the query is: >set @enddate:=now(); >set @startdate:='2014-11-01'; >set @que_id:=-1; select s.theHour as theHour,avg(s.nrAgents) as nrAgents from (select date(a.theDateHour) as theDate,extract(hour from a.theDateHour) as theHour,count(c.cc_agent_tier_status_id) as nrAgents from (
select dh.theDateHour as theDateHour, max(c.date_log) as maxdatelog,c.* FROM ( select concat(d.thedate,' ',h.theHour,':0:0') as theDateHour from ( select DATE(DATE_ADD(date(@startdate), INTERVAL @i:=@i+1 DAY) ) as theDate from (select @i:=-1) as t1 inner join cc_member_queue_end_log b on 1=1 and b.id<=datediff(@enddate,@startdate)+1 ) as d left outer join (SELECT 0 AS theHour UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23) as h on 1=1 ) AS dh left outer join cc_agents_tier_status_log as c on c.date_log<=dh.theDateHour where (if(@queue_id<0,1,0) or if(@queue_id=c.cc_queue_id,1,0)) group by dh.theDateHour,c.cc_queue_id,c.cc_agent_id,c.cc_agent_phone
) as a left outer join cc_agents_tier_status_log as c on c.date_log=a.maxdatelog and c.cc_queue_id=a.cc_queue_id and c.cc_agent_id=a.cc_agent_id and c.cc_agent_phone=a.cc_agent_phone and c.cc_agent_tier_status_id=2 group by a.theDateHour order by date(a.theDateHour),extract(hour from a.theDateHour)) as s group by s.theHour order by s.theHour;
This query takes 20 seconds to populate.
Table cc_agents_tier_status_log contains log entries of agent_id login/logout per queue per phone. status_id can have value 1 (logged out) and 2 (login) at date_log datetime.
The resulting table must contain average number of agents logged in at every hour per startdate to enddate.
Hope for some hints. Thank you. -- Mimiko desu.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Nov 14
Peter Brawley Re: Help optimize query.
Nov 14, 2014; 17:06
Peter Brawley
Re: Help optimize query.
Nov 15
Mimiko Re: Help optimize query.
Nov 15, 2014; 16:09
Mimiko
Re: Help optimize query.
Dec 01
shawn l.green Re: Help optimize query.
Dec 01, 2014; 23:57
shawn l.green
Re: Help optimize query.
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!