On Feb 25, 7:07 pm, Chris Mulligan <[email protected]> wrote: > What in the world are you doing that's generated 6000 tickets a day? > How can you possibly use the system at that rate?
600 not 6000. faxes mostly. We have ~25 people processing these tasks. Would anybody familiar with trac database structure confirm or deny that switching the ticket_custom from text to varchar will allow for substantial performance increase? Thanks, Lucas > > 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 > > tickethttp://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 > > athttp://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.
