Slow mySQL Queries

Hi!

We’re using rt 3.0.9 on mysql 4.0.18, and our users suffer from poor
performance of the mysql queries:

#v+

Query_time: 24 Lock_time: 0 Rows_sent: 19 Rows_examined: 134843

SELECT DISTINCT main.* FROM Groups main, Principals Principals_1, ACL ACL_2 WHERE ((ACL_2.RightName = ‘OwnTicket’)OR(ACL_2.RightName = ‘Sup
erUser’)) AND
( ( ACL_2.PrincipalId = Principals_1.id
AND ACL_2.PrincipalType = 'Group’
AND ( main.Domain = 'SystemInternal’
OR main.Domain = 'UserDefined’
OR main.Domain = ‘ACLEquivalence’)
AND main.id = Principals_1.id)
OR ( ( (main.Domain = ‘RT::Queue-Role’ AND main.Instance = 10) OR ( main.Domain = ‘RT::Ticket-Role’ AND main.Instance = 35476)
) AND main.Type = ACL_2.PrincipalType AND main.id = Principals_1.id) )
AND (ACL_2.ObjectType = ‘RT::System’ OR (ACL_2.ObjectType = ‘RT::Queue’ AND ACL_2.ObjectId = 10) ) ORDER BY main.Name ASC;
#v-

The database is running on a Intel® Pentium® 4 CPU 2.66GHz System with 1GB
Ram and myisam tables.

Is there any way to optimise this queries or get some new indices in
it? The query above always happens when you answer a ticket, so you
have to wait 15-30 sec before the answer form appears.

(Webserver is a different machine with apache 1.3 and fcgi.)

Sebastian

Hi!

We’re using rt 3.0.9 on mysql 4.0.18, and our users suffer from poor
performance of the mysql queries:

[snip]

The database is running on a Intel® Pentium® 4 CPU 2.66GHz System
with 1GB
Ram and myisam tables.
^^^^^^^^^^^^^

You will most likely get better performance if You change that to
innodb tables and tune MySQL according to
http://www.innodb.com/ibman.html

Regards,
Harald

Am 22.03.2004 um 12:40 schrieb Sebastian Wiesinger:

Hi!

We’re using rt 3.0.9 on mysql 4.0.18, and our users suffer from poor
performance of the mysql queries:

[snip]

The database is running on a Intel® Pentium® 4 CPU 2.66GHz System
with 1GB
Ram and myisam tables.
^^^^^^^^^^^^^

You will most likely get better performance if You change that to
innodb tables and tune MySQL according to
http://www.innodb.com/ibman.html

I didn’t get better performance from converting to innodb, it seems to
be a little bit slower now, when I display a ticket I see some queries
like:

SELECT main.Id AS id, main.Filename AS filename, main.ContentType AS
contenttype, main.Headers AS headers, main.Subject AS subject, main.Parent AS
parent, main.ContentEncoding AS contentencoding, main.ContentType AS
contenttype, main.TransactionId AS transactionid FROM Attachments main WHERE
((main.Filename IS NOT NULL)AND(main.Filename != ‘’)) AND ((main.TransactionId
= ‘121606’)) ORDER BY main.id ASC

Which take a few seconds to complete (Status is “Sorting” most of the time.)

Regards,
Sebastian