Switching from users to dev. Hello,
I was wondering if people could help me on this issue. We found that searching anything in one of the custom fields takes 7 seconds per query when you have 140K+ tickets. We are adding 600 tickets a day, so soon this query time will be even higher. 8:19:26 Query_time: 7 Lock_time: 0 Rows_sent: 0 Rows_examined: 276682 8:23:00 Query_time: 10 Lock_time: 0 Rows_sent: 0 Rows_examined: 276692 Due to such a long time, I found a ticket http://trac.edgewall.org/ticket/6986 which says, that if you replace the TEXT field with VARCHAR then you can go from 7 seconds to 0.1 seconds. In addition "It seems that access to TEXT columns in mysql requires a table access rather than an index access." (http://forums.mysql.com/ read.php?24,105964,105964#msg-105964) and TEXT field cannot be stored in memory in mysql, compared to varchar that can. Is the #6986 there are other changes not related to ticket_custom; I only care about the following query. Any pointers would be welcome. SELECT COUNT(*) FROM (SELECT t.id AS id,t.summary AS summary,t.status AS status,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.component AS component,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,contract_number.value AS contract_number FROM ticket AS t LEFT OUTER JOIN ticket_custom AS contract_number ON (id=contract_number.ticket AND contract_number.name='contract_number') LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE COALESCE(t.status,'') IN ('accepted','assigned','closed','new','reopened') AND COALESCE(contract_number.value,'') LIKE '%123456%' ESCAPE '/' ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS signed),t.id) AS foo; Thanks, Lucas -- You received this message because you are subscribed to the Google Groups "Trac Development" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/trac-dev?hl=en.
