Edgewall Software
Modify

Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#12113 closed defect (fixed)

Performance regression in query with custom fields after 1.0.2 on MySQL

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:
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:

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        

Attachments (0)

Change History (5)

comment:1 Changed 3 years ago by Jun Omae

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 Changed 3 years ago by Jun Omae

Description: modified (diff)

comment:3 Changed 3 years ago by Jun Omae

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 Changed 3 years ago by Jun Omae

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

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

comment:5 in reply to:  1 Changed 3 years ago by Jun Omae

Replying to jomae:

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

I found regression #12175.

SQLite 32-bit 31 32
SQLite 64-bit 63 64

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Jun Omae.
The resolution will be deleted.
to The owner will be changed from Jun Omae to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.