On Wednesday, November 14, 2012 at 10:40:32 AM UTC-6, Lukasz Szybalski
wrote:
>
> 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?
>
>
Hello,
I wanted to follow up on this inquiry from 2 years ago.
We are at a point where our trac holds 1.1 million tickets and searching
for custom_field takes 14seconds.
I have modified the query.py starting at line 540 in trac 0.12.3 (Debian
stable)
if mode == '~':
value = '%' + value + '%'
elif mode == '^':
value = value + '%'
elif mode == '$':
value = '%' + value
#Commented out by Lucas on 2015-01-08 return ("COALESCE(%s,'')
%s%s" % (col, neg and 'NOT ' or '',
return ("%s %s%s" % (col, neg and 'NOT ' or '',
db.like()),
(value, ))
This changes the query from 14seconds to about 1seconds.
Does anybody see a problem why this would not be a good idea?
Thanks
Lucas
--
You received this message because you are subscribed to the Google Groups "Trac
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/trac-users.
For more options, visit https://groups.google.com/d/optout.
