Slow Query

RT Users
1

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

2

I am reposting in case anyone missed my previous postOn 8/17/07, Asif Iqbal vadud3@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

3

Asif Iqbal wrote:

I am reposting in case anyone missed my previous post

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