Hello, So it looks like this is a problem: SELECT * FROM ticket AS t LEFT OUTER JOIN ticket_custom AS custom_number ON (id=custom_number.ticket AND custom_number.name='custom_number') whERE *COALESCE(custom_number.value,'') LIKE '%3042183%' *ORDER BY t.id ,custom_number.ticket;
then 1 row in set (7.03 sec) Vs SELECT * FROM ticket AS t LEFT OUTER JOIN ticket_custom AS custom_number ON (id=custom_number.ticket AND custom_number.name='custom_number') whERE *custom_number.value LIKE '%3042181%' *ORDER BY t.id,custom_number.ticket; 2 rows in set (0.27 sec) Where is this query stored in? Can I remove the "*COALESCE"?* Is it required? Should I create a ticket for this? Thanks, Lucas On Wed, Nov 14, 2012 at 8:32 AM, Lukasz Szybalski <[email protected]>wrote: > Hello, > I have this slow query....would anybody know how I can create additional > "index for my custom field in mysql? > > > # Time: 121114 8:24:07 > # User@Host: trac[trac] @ server1.local [xx.xx.xx.xx] > # Query_time: 7.861054 Lock_time: 0.000177 Rows_sent: 1 Rows_examined: > 1378153 > SET timestamp=1352903047; > SELECT t.id AS id,t.summary AS summary,t.milestone AS milestone,t.version > AS version,t.owner AS owner,t.priority AS priority,t.component AS > component,t.resolution AS resolution,t.keywords AS keywords,t.changetime AS > changetime,t.status AS status,t.time AS time,priority.value AS > priority_value,custom_number.value AS custom_number > FROM ticket AS t > LEFT OUTER JOIN ticket_custom AS custom_number ON (*id=custom_number.ticket > AND custom_number.name='custom_number'*) > LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND > priority.name=priority) > *WHERE COALESCE(custom_number.value,'') LIKE '%3042184%'* COLLATE > utf8_general_ci ESCAPE '/' > ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS signed), > t.id; > > > What would be a structure of this index ? Has anybody created one? > > CREATE INDEX MYCUSTOM_IDX ON ......???? > > > Any help on this would be appreciated. > > Thanks > Lucas > -- You received this message because you are subscribed to the Google Groups "Trac Users" 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-users?hl=en.
