[rt-users] Re: Slow Query

[Asif Iqbal]

I am reposting in case anyone missed my previous post

On 8/17/07, Asif Iqbal <vadud3 at gmail.com> 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