Edgewall Software

Opened 9 years ago

Closed 9 years ago

Last modified 9 years ago

#12113 closed defect (fixed)

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

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

Use JOIN clause when custom fields used in custom query is less than 30 in order to fix performance regressions on MySQL.

API Changes:
Internal Changes:

Description (last modified by Jun Omae)

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:

MySQL SQLite PostgreSQL
Trac 1.0.1 0.319s 0.294s 0.497s
Trac 1.0.2 8.392s 0.299s 0.511s

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 (4)

comment:1 by Jun Omae, 9 years ago

Owner: set to Jun Omae
Status: newassigned

Proposed changes in jomae.git@t12113. In the changes, it uses JOIN clauses for 30 less than custom fields.

Limitation of joins:

SQLite 32-bit 32
SQLite 64-bit 64
MySQL 61
PostgreSQL no limitation

comment:2 by Jun Omae, 9 years ago

Description: modified (diff)

comment:3 by Jun Omae, 9 years ago

I'll push the following changes for 1.0-stable and trunk.

If anyone want to fix on 0.12-stable, you can retrieve the fix in the following link.

comment:4 by Jun Omae, 9 years ago

Release Notes: modified (diff)
Resolution: fixed
Status: assignedclosed

Committed in [14140] and merged to trunk in [14141].

Note: See TracTickets for help on using tickets.