Edgewall Software

Opened 9 years ago

Last modified 9 years ago

#12113 closed defect

Performance regression in query with custom fields after 1.0.2 on MySQL — at Initial Version

Reported by: Jun Omae Owned by:
Priority: normal Milestone: 1.0.7
Component: query system Version: 0.12.6
Severity: normal Keywords: mysql
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:

Description

Performance regression in query with custom fields is reported at gmessage:trac-users:icj0UtCavgU/2NevFXTgYREJ.

That issue is leaded by changes in #11140 to avoid limitation of joins in query with many custom fields.

Timing of status=new|closed|reopended|assigned|accepted&summary=~que&col=summary&col=blocked_by&col=due_date&col=parent for 400,000 tickets with 3 custom fields:

Trac 1.0.1 0.319s
Trac 1.0.2 8.392s

Trac 1.0.1

SELECT t.id AS id,t.summary AS summary,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,`blocked_by`.value AS `blocked_by`,`due_date`.value AS `due_date`,`parent`.value AS `parent`
FROM ticket AS t
  LEFT OUTER JOIN ticket_custom AS `blocked_by` ON (id=`blocked_by`.ticket AND `blocked_by`.name='blocked_by')
  LEFT OUTER JOIN ticket_custom AS `due_date` ON (id=`due_date`.ticket AND `due_date`.name='due_date')
  LEFT OUTER JOIN ticket_custom AS `parent` ON (id=`parent`.ticket AND `parent`.name='parent')
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
WHERE (COALESCE(t.status,'') IN ('new','closed','reopended','assigned','accepted') AND (COALESCE(t.summary,'') LIKE '%que%' COLLATE utf8mb4_general_ci ESCAPE '/'))
ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS signed),t.id
id | select_type | table      | type   | possible_keys | key     | key_len | ref                           | rows   | Extra         
1  | SIMPLE      | t          | ALL    |               |         |         |                               | 399945 | Using where; Using temporary; Using filesort
1  | SIMPLE      | blocked_by | eq_ref | PRIMARY       | PRIMARY | 506     | trac_tickets.t.id,const       | 1      |               
1  | SIMPLE      | due_date   | eq_ref | PRIMARY       | PRIMARY | 506     | trac_tickets.t.id,const       | 1      |               
1  | SIMPLE      | parent     | eq_ref | PRIMARY       | PRIMARY | 506     | trac_tickets.t.id,const       | 1      |               
1  | SIMPLE      | priority   | eq_ref | PRIMARY       | PRIMARY | 1004    | const,trac_tickets.t.priority | 1      |               

Trac 1.0.2

SELECT t.id AS id,t.summary AS summary,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,t.`blocked_by` AS `blocked_by`,t.`due_date` AS `due_date`,t.`parent` AS `parent`
FROM (
  SELECT t.id AS id,t.summary AS summary,t.status AS status,t.priority AS priority,t.time AS time,t.changetime AS changetime,
  (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='blocked_by') AS `blocked_by`,
  (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='due_date') AS `due_date`,
  (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='parent') AS `parent`
  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','closed','reopended','assigned','accepted') AND (COALESCE(t.summary,'') LIKE '%que%' COLLATE utf8mb4_general_ci ESCAPE '/'))
ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS signed),t.id
id | select_type        | table      | type   | possible_keys | key     | key_len | ref               | rows   | Extra              
1  | PRIMARY            | <derived2> | ALL    |               |         |         |                   | 400000 | Using where; Using temporary; Using filesort
1  | PRIMARY            | priority   | eq_ref | PRIMARY       | PRIMARY | 1004    | const,t.priority  | 1      |                    
2  | DERIVED            | t          | ALL    |               |         |         |                   | 399945 |                    
5  | DEPENDENT SUBQUERY | c          | eq_ref | PRIMARY       | PRIMARY | 506     | trac_tickets.t.id | 1      | Using where        
4  | DEPENDENT SUBQUERY | c          | eq_ref | PRIMARY       | PRIMARY | 506     | trac_tickets.t.id | 1      | Using where        
3  | DEPENDENT SUBQUERY | c          | eq_ref | PRIMARY       | PRIMARY | 506     | trac_tickets.t.id | 1      | Using where        

Change History (0)

Note: See TracTickets for help on using tickets.