Edgewall Software
Modify

Opened 6 years ago

Last modified 5 years ago

#11206 new enhancement

Reduce join in query process

Reported by: VEOVUSH@… Owned by:
Priority: normal Milestone: next-major-releases
Component: query system Version: 1.0dev
Severity: normal Keywords: mysql, performance
Cc: Branch:
Release Notes:
API Changes:

Description (last modified by Jun Omae)

Hi,

We are running Trac with 10k tickets, and some modules need query through TracQuery a lot, but we found backend MySQL's load is heavy.

From show processlist, we found join query of "copy to tmp table" action between tables may cost a lot of time.

We debugged the SQL below:

SELECT COUNT(*) FROM (SELECT t.id AS id,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.resolution AS resolution,t.time AS time,t.changetime AS changetime,t.summary AS summary,priority.value AS priority_value
FROM ticket AS t
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
WHERE ((COALESCE(t.summary,'')='querystr'))
ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS signed),t.id) AS foo

After profiling the query: Copying to tmp table cost 99.355% percentage of whole time. If ((COALESCE(t.summary,'')='querystr')) changed to t.summary = "querystr", this copy action will not take that much time. And if count only 0, problem still happens, if not count, problem not exists, maybe we can directly select the result with count. Or break this query into t.summary = '' or t.summary is null if query string is empty.

Another problem is 'priority' column are regarded as required fields even I deselect priority column, thus join will be need every query.

Maybe in default, join on priority can be removed, or deselect priority column will disable it.

Thanks! V.E.O

Attachments (0)

Change History (2)

comment:1 by Jun Omae, 6 years ago

Description: modified (diff)

comment:2 by Peter Suter, 5 years ago

Keywords: mysql performance added
Milestone: next-major-releases

Modify Ticket

Change Properties
Set your email in Preferences
Action
as new The ticket will remain with no owner.
The ticket will be disowned.
as The resolution will be set.
The owner will be changed from (none) to anonymous.

Add Comment


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