What in the world are you doing that's generated 6000 tickets a day? How can you possibly use the system at that rate?
On Thu, Feb 25, 2010 at 5:33 PM, Lukasz Szybalski <[email protected]> wrote: > 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. > > -- 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.
