Let's see the results of Explain Extended this query, & result of Show
Create Table cc_member_queue_end_log.
PB
-----
On 2014-11-13 1:34 PM, Mimiko wrote:
Hello. I have this table:
> 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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
