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 = > Transactions_1.id)AND(main.id = Transactions_1.ObjectId > ) ) ); > > # Query_time: 535 Lock_time: 0 Rows_sent: 1 Rows_examined: 1733112 > 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 = > Transactions_1.id)AND(main.id = Transactions_1.ObjectId > ) ) ); > > # Query_time: 526 Lock_time: 0 Rows_sent: 1 Rows_examined: 1733130 > 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 = > Transactions_1.id)AND(main.id = Transactions_1.ObjectId > ) ) ); > > # Query_time: 528 Lock_time: 0 Rows_sent: 0 Rows_examined: 1733137 > SELECT DISTINCT main.* FROM Tickets main , Transactions > Transactions_1, Attachments Attachments_2 WHERE > ((Transactions_1.ObjectTyp > e = 'RT::Ticket')) AND ((main.EffectiveId = main.id)) AND > ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( ( > (Attach > ments_2.Content LIKE '%txcomber%')AND(Attachments_2.TransactionId = > Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) ) O > RDER BY main.id ASC; > > # Query_time: 2095 Lock_time: 0 Rows_sent: 1 Rows_examined: 0 > SELECT GET_LOCK('Apache-Session-d7ca7d5da9351479f35fdc4b88daa536', 3600); > > # Query_time: 541 Lock_time: 0 Rows_sent: 1 Rows_examined: 1733137 > 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 = > Transactions_1.id)AND(main.id = Transactions_1.ObjectId > ) ) ); > > > RT logs: > > [Fri Aug 17 13:16:13 2007] [warning]: Duplicate specification "" for > option "" (/opt/rt3/lib/RT.pm:287) > [Fri Aug 17 13:16:13 2007] [warning]: Queue->CustomFields is > deprecated, use Queue->TicketCustomFields instead at > (main:/usr/local/bin/rt:682) at /opt/rt3/lib/RT/Queue_Overlay.pm line > 524. (/opt/rt3/lib/RT.pm:287) > [Fri Aug 17 13:16:15 2007] [warning]: Use of uninitialized value in > pattern match (m//) at /usr/local/lib/perl5/5.8.6/Getopt/Long.pm line > 315. (/opt/rt3/lib/RT.pm:287) > [Fri Aug 17 13:16:15 2007] [warning]: Use of uninitialized value in > string eq at /usr/local/lib/perl5/5.8.6/Getopt/Long.pm line 317. > (/opt/rt3/lib/RT.pm:287) > [Fri Aug 17 13:16:15 2007] [warning]: Use of uninitialized value in > pattern match (m//) at /usr/local/lib/perl5/5.8.6/Getopt/Long.pm line > 686. (/opt/rt3/lib/RT.pm:287) > > > -- > Asif Iqbal > PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu >
-- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu _______________________________________________ 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