Reduce join in query process

[VEOVUSH@…]

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