Re: Help optimize query.
Hello Mimko, Sorry for the late reply. I had a bunch of work to take care of before vacation, then there was the vacation itself. :) On 11/13/2014 2: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: TableNon_uniqueKey_nameSeq_in_indexColumn_name Collation CardinalitySub_partPackedNullIndex_type CommentIndex_comment cc_agents_tier_status_log0PRIMARY1idA 23999(null)BTREE(null) (null) cc_agents_tier_status_log1IDX_cc_agents_tier_status_log_21 cc_agentA260(null)BTREE(null)(null) cc_agents_tier_status_log1IDX_cc_agents_tier_status_log_31 date_logA23999(null)BTREE(null)(null) cc_agents_tier_status_log1FK_cc_agents_tier_status_log_21 cc_agent_idA2(null)BTREE(null)(null) cc_agents_tier_status_log1FK_cc_agents_tier_status_log_31 cc_queue_idA14(null)BTREE(null)(null) cc_agents_tier_status_log1FK_cc_agents_tier_status_log_11 cc_agent_tier_status_idA2(null)BTREE (null)(null) cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_71 idA23999(null)BTREE(null)(null) cc_agents_tier_status_log1IDX_cc_agents_tier_status_log_72 date_logA23999(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_id0,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. The first problem is that you are generating a lot of extra rows before you actually need them. The only place where you should be faking the
Re: Help optimize query.
On 15.11.2014 01:06, Peter Brawley wrote: Let's see the results of Explain Extended this query, result of Show Create Table cc_member_queue_end_log. cc_member_queue_end_log is not of interest, it is used just as a series of numbers. It may be any table with ids. I've changed a bit the query which seemed to reduce the select time, but not for a lot. set @enddate:=now(); set @startdate:='2014-11-01'; set @que_id:=-1; explain extended select s.theHour as theHour,avg(s.nrAgents) as nrAgents from - (select date(FROM_UNIXTIME(a.theDateHour)) as theDate,extract(hour from FROM_UNIXTIME(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 UNIX_TIMESTAMP(concat(d.thedate,' ',h.theHour,':0:0')) as theDateHour - from - ( select DATE(DATE_ADD(date('2014-11-01'), INTERVAL @i:=@i+1 DAY) ) as theDate from (select @i:=-1) as t1 - inner join cc_agents_tier_status_log b on 1=1 and b.id=datediff(now(),'2014-11-01')+1 ) as d - straight_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 - straight_join - cc_agents_tier_status_log as c - on UNIX_TIMESTAMP(c.date_log)=dh.theDateHour where (if(-10,1,0) or if(-1=c.cc_queue_id,1,0)) - group by dh.theDateHour,c.cc_queue_id,c.cc_agent_id,c.cc_agent_phone - - - ) as a - straight_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(FROM_UNIXTIME(a.theDateHour)),extract(hour from FROM_UNIXTIME(a.theDateHour))) - as s - group by s.theHour - order by s.theHour\G *** 1. row *** id: 1 select_type: PRIMARY table: derived2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 360 filtered: 100.00 Extra: Using temporary; Using filesort *** 2. row *** id: 2 select_type: DERIVED table: derived3 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 43560 filtered: 100.00 Extra: Using temporary; Using filesort *** 3. row *** id: 2 select_type: DERIVED table: c type: ref possible_keys: IDX_cc_agents_tier_status_log_3,FK_cc_agents_tier_status_log_2,FK_cc_agents_tier_status_log_3,FK_cc_agents_tier_status_log_1 key: IDX_cc_agents_tier_status_log_3 key_len: 4 ref: a.maxdatelog rows: 1 filtered: 100.00 Extra: Using where *** 4. row *** id: 3 select_type: DERIVED table: derived4 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 360 filtered: 100.00 Extra: Using temporary; Using filesort *** 5. row *** id: 3 select_type: DERIVED table: c type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 24207 filtered: 100.00 Extra: Using where; Using join buffer *** 6. row *** id: 4 select_type: DERIVED table: derived5 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 15 filtered: 100.00 Extra: *** 7. row *** id: 4 select_type: DERIVED table: derived7 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 24 filtered: 100.00 Extra: Using join buffer *** 8. row *** id: 7 select_type: DERIVED table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: No tables used *** 9. row *** id: 8 select_type: UNION table: NULL
Re: Help optimize query.
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: TableNon_uniqueKey_nameSeq_in_indexColumn_name Collation CardinalitySub_partPackedNull Index_type CommentIndex_comment cc_agents_tier_status_log0PRIMARY1idA 23999(null)BTREE(null) (null) cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_21 cc_agentA 260(null)BTREE(null)(null) cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_31 date_logA 23999(null)BTREE(null)(null) cc_agents_tier_status_log1 FK_cc_agents_tier_status_log_21 cc_agent_idA 2(null)BTREE(null)(null) cc_agents_tier_status_log1 FK_cc_agents_tier_status_log_31 cc_queue_idA 14(null)BTREE(null)(null) cc_agents_tier_status_log1 FK_cc_agents_tier_status_log_11 cc_agent_tier_status_id A2(null)BTREE(null) (null) cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_7 1 idA23999(null)BTREE(null) (null) cc_agents_tier_status_log1 IDX_cc_agents_tier_status_log_72 date_logA 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_id0,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