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_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.
The first problem is that you are generating a lot of extra rows befo
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(-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:
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:
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:
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:
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:
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: NUL
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_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:htt
Help optimize query.
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.
--
Mimiko desu.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: need help optimize query
Your original query (implicit INNER JOINs): SELECT teu.name, eca.owner_id, ece.value FROM typed_enterprise_unit teu, e_contact_association eca, e_contact_entry ece WHERE teu.unit_id=eca.owner_id and eca.entry_id=ece.entry_id and eca.type_id=68 and (teu.type_path like '%/66/%' or teu.type_id=66) ORDER BYeca.owner_id LIMIT 22; The same query reformatted to demonstrate explicit INNER JOINS SELECT teu.name , eca.owner_id , ece.value FROM typed_enterprise_unit teu INNER JOIN e_contact_association eca ON teu.unit_id=eca.owner_id INNER JOIN e_contact_entry ece ON eca.entry_id=ece.entry_id WHERE eca.type_id=68 AND (teu.type_path like '%/66/%' or teu.type_id=66) ORDER BYeca.owner_id LIMIT 22; I have two immediate suggestions. 1) If you had not attached your information but included it in your message as text, you would have probably already received an answer. Next time, please inline your information. Please don't use an attachment as many people WILL NOT OPEN THEM unless they know you. 2) Be very, very careful when you use a comma delimited list of tables to imply INNER JOIN that you actually include the necessary join conditions in your WHERE clause. You did do that this time. However, it is a well known hazard of that particular query format that those terms can be accidentally omitted and you can very easily wind up with an unintentional Cartesian product of your tables. The explain from your query (extracted from your attachment and included below) tells me exactly why your query takes so long (heavily trimmed to avoid excessive message wrapping). +--+---+--++-++-+--+ |id|select_type|table |type|possible_keys|key | | Extra| +--+---+--++-++-+--+ | 1| PRIMARY |eca |ALL |NULL |NULL| | Using where; Using temporary; Using filesort | | 1| PRIMARY |ece |ALL |NULL |NULL| | Using where| | 1| PRIMARY ||ALL |NULL |NULL| | Using where| | 2| DERIVED |tp|ALL |NULL |NULL| || | 2| DERIVED |eu|ALL |NULL |NULL| | Using where| +--+---+--++-++-++ Look at the column "possible_keys". Every entry is NULL. That means that you have no indexes on your tables that could have been used to respond to this query. This worries me as tables that are involved in relationships with other tables should at a MINIMUM contain a primary key. Your slow performance is due to the fact that the query engine had to perform complete table scans of all tables involved in this query. May I strongly suggest some reading for you. If you don't understand any part of it, come back to the list with your questions and we can help explain it in other ways. These articles describe ways to implement indexes(keys) in your database to speed up your queries. http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html This article describes how to interpret the output of the EXPLAIN command http://dev.mysql.com/doc/mysql/en/EXPLAIN.html These articles help to explain how queries are helped by indexes http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html http://dev.mysql.com/doc/mysql/en/SELECT_speed.html There are also MANY articles on the web about query optimization and index usage. Use your favorite search engine to help you find them. Nearly all of the principles and techniques that work for the other database engines (Oracle, MS SQL Server, Informix, etc) will also work for your queries with MySQL so don't necessarily limit yourself to just MySQL articles. To solve your speed problem, you need to create an appropriate set of keys(indexes) on your tables. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Elim Qiu" <[EMAIL PROTECTED]> wrote on 11/18/2004 07:44:01 PM: > Dear list, > > i have some small tables but for some reason the mysql took very long to > find the results. my query looks > like below and mysql'e explain is attached for better format. Thanks for > your help! > > select teu.name, eca.owner_id, ece.value > > fromtyped_enterprise_unit teu, > e_contact_association eca, > e_contact_entry ece > > where teu.unit_id=eca.owner_id and eca.entry_id=ece.entry_id and > eca.type_id=68 and (teu.type_path like '%/66/%' or teu.type_id=66) > and eca.owner_id > 45 > > order by eca.owner_id limit 50; > [attachment "need_help_query.txt" deleted by Shawn Green/Unimin] No > virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.289 / Virus Database: 265.3.1 - Release Date: 11/15/2004 > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMA
need help optimize query
Dear list, i have some small tables but for some reason the mysql took very long to find the results. my query looks like below and mysql'e explain is attached for better format. Thanks for your help! select teu.name, eca.owner_id, ece.value fromtyped_enterprise_unit teu, e_contact_association eca, e_contact_entry ece where teu.unit_id=eca.owner_id and eca.entry_id=ece.entry_id and eca.type_id=68 and (teu.type_path like '%/66/%' or teu.type_id=66) and eca.owner_id > 45 order by eca.owner_id limit 50; mysql> select teu.name, eca.owner_id, ece.value -> -> fromtyped_enterprise_unit teu, -> e_contact_association eca, -> e_contact_entry ece -> -> where teu.unit_id=eca.owner_id and eca.entry_id=ece.entry_id and -> eca.type_id=68 and (teu.type_path like '%/66/%' or teu.type_id=66) -> -> order by eca.owner_id limit 22; +---+--++ | name | owner_id | value | +---+--++ | Asian Book One| 45 | [EMAIL PROTECTED]| +---+--++ 22 rows in set (4.97 sec) mysql> explain select teu.name, eca.owner_id, ece.value -> -> fromtyped_enterprise_unit teu, -> e_contact_association eca, -> e_contact_entry ece -> -> where teu.unit_id=eca.owner_id and eca.entry_id=ece.entry_id and -> eca.type_id=68 and (teu.type_path like '%/66/%' or teu.type_id=66) -> -> order by eca.owner_id limit 22; ++-++--+---+--+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++--+---+--+-+--+--+-+ | 1 | PRIMARY | eca| ALL | NULL | NULL | NULL| NULL | 2712 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | ece| ALL | NULL | NULL | NULL| NULL | 2669 | Using where | | 1 | PRIMARY | | ALL | NULL | NULL | NULL| NULL | 1440 | Using where | | 2 | DERIVED | tp | ALL | NULL | NULL | NULL| NULL | 100 | | | 2 | DERIVED | eu | ALL | NULL | NULL | NULL| NULL | 1444 | Using where | ++-++--+---+--+-+--+--+-+ 5 rows in set (0.37 sec) No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.289 / Virus Database: 265.3.1 - Release Date: 11/15/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
