Asif Iqbal wrote:
I am reposting in case anyone missed my previous post
On 8/17/07, Asif Iqbal <[EMAIL PROTECTED]> wrote:
I have few queries that are extremely slow. I am using RT 3.4.5, Perl
5.8.6, Solaris 10 x86, Apache/1.3.33 (Unix) mod_perl/1.29
mod_ssl/2.8.22 OpenSSL/0.9.7g, mysql 4.0.24, DBIx::SearchBuilder 1.40.
Is there any tweak to fix the slow querry?
Slow Query Logs:
# Query_time: 558 Lock_time: 0 Rows_sent: 0 Rows_examined: 2442535
SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2 WHERE ((Transactions_1.O
bjectType = 'RT::Ticket')) AND ((main.EffectiveId = main.id)) AND
((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( (
(Attachments_2.Content LIKE
'%txcomber%')AND(Attachments_2.TransactionId =
This is your culprit and can't do nothing about it.
You're asking your DB engine to search through all your
ticketbodies/attachments WITHOUT using an index and without realising
this is a huge text column and therefor needs something special, like
OracleText or PgText.
Transactions_1.id)AND(main.id = Transactions_1.ObjectId
) ) );
Joop
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
