[rt-users] extremely slow rt response, query hanging

[Ruslan Zakirov]

The query is problem for ages :(
There is several solutions:
1) turn off search by attachment content;
2) enable this condition only when applied conditions limit result set
to the sane number of tickets(for example 1000);
3) add additional conditions automaticaly. it was suggested that when
people search in attachments content they always want search in the
text of the message (not in a files), so we could add additinal
conditions "Filename IS NULL AND ContentType = 'text/plain'" to this
condition. This is not final solution, but could be part of;
4) rework RT DB and split text attachments (really attachments which
RT parses and converts to UTF-8) from all attachments, this would
allow us to use FULLTEXT indexes, but it's task for future major
versions.


On 12/7/05, Harald Wagener <hwagener at hamburg.fcb.com> wrote:
> Hello,
> One of our users constructed a search on our RT 3.2.2 instance which
> is slowing the system down quite a lot:
>
> SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
> Transactions_1, Attachments Attachments_2  WHERE ((main.EffectiveId =
> main.id)) AND ((main.Status != 'deleted')) AND ((main.Type =
> 'ticket')) AND ((main.Subject LIKE '%Performance%')OR
> ( (Attachments_2.Content LIKE '%Performance%')AND
> (Attachments_2.TransactionId = Transactions_1.id)AND(main.id =
> Transactions_1.Ticket) ) )
>
> I have the feeling that 'something' in there is less than ideal, and
> would like to get some input by you folks as to what can be done to
> speed that up (I found two queries like this hogging the cpu for 200
> hours.
>
> The database is MySQL 4.0.24. I updated DBIx::Searchbuilder to
> version 1.33 from 1.27, with no difference in results. The database
> contains roughly 2400 tickets.
>
> Any help is appreciated.
>
> Regards,
>      Harald
>
> --
> Harald Wagener
> Technischer Leiter
>
> Foote Cone & Belding
> FCB Wilkens
> An der Alster 42
> 20099 Hamburg
> Germany
>
> T: +49 (0)40 2881 1252
> F: +49 (0)40 2881 1217
> hwagener at hamburg.fcb.com
> http://www.footeconebelding.de
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Be sure to check out the RT Wiki at http://wiki.bestpractical.com
>
> Download a free sample chapter of RT Essentials from O'Reilly Media at http://rtbook.bestpractical.com
>
> WE'RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston and
> San Francisco - Find out more at http://bestpractical.com/services/training.html
>


--
Best regards, Ruslan.