Performance regression in query with custom fields after 1.0.2 on MySQL

[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        

[Jun Omae]

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