On Thu, Jul 16, 2015 at 10:29 PM, Lukasz Szybalski <[email protected]> wrote:
> > > 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]> 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. > I believe Jun already fixed the issue in Trac 1.0.7, which was released a few hours ago, so please evaluate the performance of the latest version with your queries. - Ryan -- 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.
