> >> 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. >
Hello, Could you point me to the diff for the query.py that fixes this?. I am running a debian stable for last 7 years and we are not planning on upgrading to next trac until debian does it which will probably in in 2 years based on their prior schedule or at least until its in a jessie-backports if really necessary. I can modify the query.py manually to fix the problem above if need be; if you could point me to exact changes that I need to copy over? Is it this it or?? http://trac.edgewall.org/changeset/14140 Also, Is that issue going to resolve "my tickets" query? See the output from slow-query.log Its taking 23 seconds to check "my tickets". Between my speed went from myisam 2-3 sec in 0.12.5 to 27sec innodb in Trac 1.0.2 <http://uicesv15/it/about> # Time: 150916 14:30:47 # User@Host: trac[trac] @ xxxx [xxxxx] # Query_time: 15.664268 Lock_time: 0.000249 Rows_sent: 1 Rows_examined: 0 use trac; SET timestamp=1442431847; SELECT COUNT(*) FROM (SELECT t.id AS id,t.summary AS summary,t.status AS status,t.priority AS priority,t.component AS component,t.version AS version,t.keywords AS keywords,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,t.owner AS owner,priority.value AS priority_value,t.`policy_number` AS `policy_number` FROM ( SELECT t.id AS id,t.summary AS summary,t.status AS status,t.priority AS priority,t.component AS component,t.version AS version,t.keywords AS keywords,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,t.owner AS owner, (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) LEFT OUTER JOIN milestone ON (milestone.name=milestone) WHERE ((COALESCE(t.owner,'')='user12') AND COALESCE(t.status,'') IN ('accepted','assigned','new','reopened')) ORDER BY COALESCE(t.milestone,'')='',COALESCE(milestone.completed,0)=0,milestone.completed,COALESCE(milestone.due,0)=0,milestone.due,t.milestone,COALESCE(priority.value,'')='',CAST(priority.value AS signed),t.id) AS x; # Time: 150916 14:31:03 # User@Host: trac[trac] @ xxxx [xxxxx] # Query_time: 16.354391 Lock_time: 0.000256 Rows_sent: 32 Rows_examined: 4005081 SET timestamp=1442431863; SELECT t.id AS id,t.summary AS summary,t.status AS status,t.priority AS priority,t.component AS component,t.version AS version,t.keywords AS keywords,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,t.owner AS owner,priority.value AS priority_value,t.`policy_number` AS `policy_number` FROM ( SELECT t.id AS id,t.summary AS summary,t.status AS status,t.priority AS priority,t.component AS component,t.version AS version,t.keywords AS keywords,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,t.owner AS owner, (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) LEFT OUTER JOIN milestone ON (milestone.name=milestone) WHERE ((COALESCE(t.owner,'')='user12') AND COALESCE(t.status,'') IN ('accepted','assigned','new','reopened')) ORDER BY COALESCE(t.milestone,'')='',COALESCE(milestone.completed,0)=0,milestone.completed,COALESCE(milestone.due,0)=0,milestone.due,t.milestone,COALESCE(priority.value,'')='',CAST(priority.value AS signed),t.id; Thanks Lucas -- 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.
