On Wednesday, June 24, 2015 at 10:17:35 PM UTC-5, Jun Omae wrote:
>
> Hi,
>
> On Thu, Jun 25, 2015 at 4:25 AM, Peter Suter <[email protected]
> <javascript:>> wrote:
> > I'm assuming this is a ticket query. Looking at the source control
> history
> > it looks like this changed in #11140
> > http://trac.edgewall.org/ticket/11140
> > for 12.6, 1.0.2 and 1.1.2 because MySQL and SQLite limit in the number
> of
> > joins, and queries require too many.
> >
> > If this is a common performance regression, maybe Trac could use joins
> again
> > if there are only a few custom fields.
> >
> > Just out of curiosity, can you tell us what DB you are running? How
> many
> > tickets are there in total? How many matching this query?
>
> It seems to be MySQL database since backquote characters are used for
> quoting identifiers.
>
> Additionally, would you please post results of EXPLAIN command for the
> two queries?
>
> EXPLAIN
> SELECT t.id AS id,t.summary AS summary,t.version AS version,t.status
> AS status,t.priority AS priority,t.component AS component,t.keywords
> AS keywords,t.time AS time,t.changetime AS changetime,t.milestone AS
> milestone,
> (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND
> c.name='contract_number') AS `contract_number`
> FROM ticket AS t
> LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND
> priority.name=priority)
> WHERE (t.status IN ('new','reopened') AND t.version NOT IN
> ('ILC','INC') AND (t.milestone='Endorsement'))
> ;
>
> EXPLAIN
> SELECT t.id AS id,t.summary AS summary,t.version AS version,t.status
> AS status,t.priority AS priority,t.component AS component,t.keywords
> AS keywords,t.time AS time,t.changetime AS changetime,t.milestone AS
> milestone, c2.value AS `contract_number`
> FROM ticket AS t
> LEFT OUTER JOIN ticket_custom c2
> on c2.ticket=t.id
> and c2.name='contract_number'
> LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND
> priority.name=priority)
> WHERE (t.status IN ('new','reopened') AND t.version NOT IN
> ('ILC','INC') AND (t.milestone='Endorsement'))
> ;
>
>
Here are the details:
# Time: 150716 23:43:30
# User@Host: xxxx @ xxxxx[xxxx]
# Query_time: 12.570735 Lock_time: 0.000199 Rows_sent: 100 Rows_examined:
3926507
SET timestamp=1437108210;
SELECT t.id AS id,t.summary AS summary,t.version AS version,t.status AS
status,t.priority AS priority,t.component AS component,t.keywords AS
keywords,t.time AS time,t.changetime AS changetime,t.milestone AS
milestone,priority.value AS priority_value,t.`policy_number` AS
`policy_number`
FROM (
SELECT t.id AS id,t.summary AS summary,t.version AS version,t.status AS
status,t.priority AS priority,t.component AS component,t.keywords AS
keywords,t.time AS time,t.changetime AS changetime,t.milestone AS milestone,
(SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND
c.name='policy_number') AS `policy_number`
FROM ticket AS t) AS t
LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND
priority.name=priority)
WHERE (COALESCE(t.status,'') IN ('new','reopened') AND
COALESCE(t.version,'') NOT IN ('ILC','INC') AND
(COALESCE(t.milestone,'')='UW_Cancel'))
ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS signed),t.id
LIMIT 100 OFFSET 0;
see attached
Explain_slowquery20150716.csv
run: 12.856seconds
Now converted to innerjoins:
SELECT t.id AS id,t.summary AS summary,t.version AS version,t.status AS
status,t.priority AS priority,t.component AS component,t.keywords AS
keywords,t.time AS time,t.changetime AS changetime,t.milestone AS
milestone,priority.value AS priority_value,t.`policy_number` AS
`policy_number`
FROM (
SELECT t.id AS id,t.summary AS summary,t.version AS version,t.status AS
status,t.priority AS priority,t.component AS component,t.keywords AS
keywords,t.time AS time,t.changetime AS changetime,t.milestone AS milestone,
c.value AS `policy_number`
FROM ticket t
inner join ticket_custom c
on c.ticket=t.id
and c.name='policy_number'
) AS t
LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND
priority.name=priority)
WHERE (COALESCE(t.status,'') IN ('new','reopened') AND
COALESCE(t.version,'') NOT IN ('ILC','INC') AND
(COALESCE(t.milestone,'')='UW_Cancel'))
ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS signed),t.id
LIMIT 100 OFFSET 0;
Explain_innerjoin_query20150716.csv
run 9.5sec
....
I'm not an expert on queries but I wish this query could be optimized to be
used in memory. I'm not sure why mysql keeps creating the tmp table on disc
to get this query created. There is enough hardware and memory to keep the
whole thing in memory.
--
You received this message because you are subscribed to the Google Groups "Trac
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/trac-users.
For more options, visit https://groups.google.com/d/optout.
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,<derived2>,ALL,NULL,NULL,NULL,NULL,1306041,"Using where; Using temporary; Using filesort"
1,PRIMARY,priority,eq_ref,PRIMARY,PRIMARY,1000,"const,t.priority",1,
2,DERIVED,c,ALL,PRIMARY,NULL,NULL,NULL,1238973,"Using where"
2,DERIVED,t,eq_ref,PRIMARY,PRIMARY,4,trac.c.ticket,1,"Using where"
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,<derived2>,ALL,NULL,NULL,NULL,NULL,1309842,"Using where; Using temporary; Using filesort"
1,PRIMARY,priority,eq_ref,PRIMARY,PRIMARY,1000,"const,t.priority",1,
2,DERIVED,t,ALL,NULL,NULL,NULL,NULL,1162063,
3,"DEPENDENT SUBQUERY",c,eq_ref,PRIMARY,PRIMARY,504,trac.t.id,1,"Using where"
