[prev in list] [next in list] [prev in thread] [next in thread]
List: mysql
Subject: Re: Help optimize query.
From: Mimiko <vbvbrj () gmail ! com>
Date: 2014-11-15 14:09:15
Message-ID: 54675E8B.5060201 () gmail ! com
[Download RAW message or body]
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(-1<0,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
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 10. row ***************************
id: 9
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 11. row ***************************
id: 10
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 12. row ***************************
id: 11
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 13. row ***************************
id: 12
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 14. row ***************************
id: 13
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 15. row ***************************
id: 14
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 16. row ***************************
id: 15
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 17. row ***************************
id: 16
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 18. row ***************************
id: 17
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 19. row ***************************
id: 18
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 20. row ***************************
id: 19
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 21. row ***************************
id: 20
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 22. row ***************************
id: 21
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 23. row ***************************
id: 22
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 24. row ***************************
id: 23
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 25. row ***************************
id: 24
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 26. row ***************************
id: 25
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 27. row ***************************
id: 26
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 28. row ***************************
id: 27
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 29. row ***************************
id: 28
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 30. row ***************************
id: 29
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 31. row ***************************
id: 30
select_type: UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 32. row ***************************
id: NULL
select_type: UNION RESULT
table:
<union7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra:
*************************** 33. row ***************************
id: 5
select_type: DERIVED
table: <derived6>
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra:
*************************** 34. row ***************************
id: 5
select_type: DERIVED
table: b
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 16
filtered: 100.00
Extra: Using where; Using index
*************************** 35. row ***************************
id: 6
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
35 rows in set, 1 warning (15.69 sec)
select "s"."theHour" AS "theHour",avg("s"."nrAgents") AS "nrAgents" from
(select cast(from_unixtime("a"."theDateHour") as date) AS
"theDate",extract(hour from from_unixtime("a"."theDateHour")) AS
"theHour",count("freeswitch_data"."c"."cc_agent_tier_status_id") AS
"nrAgents" from (select "dh"."theDateHour" AS
"theDateHour",max("freeswitch_data"."c"."date_log") AS
"maxdatelog","freeswitch_data"."c"."id" AS
"id","freeswitch_data"."c"."date_log" AS
"date_log","freeswitch_data"."c"."cc_agent" AS
"cc_agent","freeswitch_data"."c"."cc_agent_tier_status_id" AS
"cc_agent_tier_status_id","freeswitch_data"."c"."cc_queue_id" AS
"cc_queue_id","freeswitch_data"."c"."cc_agent_id" AS
"cc_agent_id","freeswitch_data"."c"."cc_agent_phone" AS
"cc_agent_phone","freeswitch_data"."c"."cc_agent_domain" AS
"cc_agent_domain" from (select unix_timestamp(concat("d"."theDate",'
',"h"."theHour",':0:0')) AS "theDateHour" from (select
cast((cast('2014-11-01' as date) + interval (@i:=((@i) + 1)) day) as
date) AS "theDate" from "freeswitch_data"."cc_agents_tier_status_log"
"b" where ("freeswitch_data"."b"."id" <= <cache>(((to_days(now()) -
to_days('2014-11-01')) + 1)))) "d" straight_join (select 0 AS "theHour"
union all select 1 AS "1" union all select 2 AS "2" union all select 3
AS "3" union all select 4 AS "4" union all select 5 AS "5" union all
select 6 AS "6" union all select 7 AS "7" union all select 8 AS "8"
union all select 9 AS "9" union all select 10 AS "10" union all select
11 AS "11" union all select 12 AS "12" union all select 13 AS "13" union
all select 14 AS "14" union all select 15 AS "15" union all select 16 AS
"16" union all select 17 AS "17" union all select 18 AS "18" union all
select 19 AS "19" union all select 20 AS "20" union all select 21 AS
"21" union all select 22 AS "22" union all select 23 AS "23") "h") "dh"
straight_join "freeswitch_data"."cc_agents_tier_status_log" "c" where
(unix_timestamp("freeswitch_data"."c"."date_log") <= "dh"."theDateHour")
group by
"dh"."theDateHour","freeswitch_data"."c"."cc_queue_id","freeswitch_data"."c"."cc_agent_id","freeswitch_data"."c"."cc_agent_phone") \
"a" straight_join "freeswitch_data"."cc_agents_tier_status_log" "c"
where (("freeswitch_data"."c"."cc_agent_tier_status_id" = 2) and
("freeswitch_data"."c"."cc_agent_phone" = "a"."cc_agent_phone") and
("freeswitch_data"."c"."cc_agent_id" = "a"."cc_agent_id") and
("freeswitch_data"."c"."cc_queue_id" = "a"."cc_queue_id") and
("freeswitch_data"."c"."date_log" = "a"."maxdatelog")) group by
"a"."theDateHour" order by cast(from_unixtime("a"."theDateHour") as
date),extract(hour from from_unixtime("a"."theDateHour"))) "s" group by
"s"."theHour" order by "s"."theHour"
--
Mimiko desu.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic