[rt-users] Re: Slow query

[Asif Iqbal]

On 11/12/06, Asif Iqbal <vadud3 at gmail.com> wrote:
> Here is another slow query
>
> Reading mysql slow query log from webrt-mysql-slow.log
> Count: 66  Time=158.26s (10445s)  Lock=0.00s (0s)  Rows=1.0 (66),
> rt_user[rt_user]@localhost
>   SELECT GET_LOCK('S', N)
>
> Anyway to improve this?

Here are some more extreme slow queries

Reading mysql slow query log from /export/home/ibdata/webrt-mysql-slow.log
Count: 83  Time=135.24s (11225s)  Lock=0.00s (0s)  Rows=1.0 (83),
rt_user[rt_user]@localhost
  SELECT GET_LOCK('S', N)

Count: 1  Time=75.00s (75s)  Lock=0.00s (0s)  Rows=1.0 (1),
rt_user[rt_user]@localhost
  SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
Transactions_1, Attachments Attachments_2  WHERE ((Transactions_1.ObjectType
= 'S')) AND ((main.EffectiveId = main.id)) AND ((main.Status != 'S')) AND ((
main.Type = 'S')) AND ((main.Status = 'S')AND(main.Queue = 'S')AND (
(Attachments_2.Content LIKE 'S')AND(Attachments_2.TransactionId =
Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) )

Explain of the second select query gives impossible where as following

mysql> explain SELECT COUNT(DISTINCT main.id) FROM Tickets main ,
Transactions Transactions_1, Attachments Attachments_2 WHERE
((Transactions_1.ObjectType = 'S')) AND ((main.EffectiveId = main.id)) AND
((main.Status != 'S')) AND ((main.Type = 'S')) AND ((main.Status = 'S')AND(
main.Queue = 'S')AND ( (Attachments_2.Content LIKE 'S')
AND(Attachments_2.TransactionId = Transactions_1.id)AND(main.id =
Transactions_1.ObjectId) ) );

+-----------------------------+
| Comment                |
+-----------------------------+
| Impossible WHERE |
+-----------------------------+
1 row in set (0.00 sec)

Using mysql 4.0.24 and RT 3.4.5

Any suggestion on how to improve that

>
> --
> Asif Iqbal
> PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu
>


-- 
Asif Iqbal
PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20061130/952398c6/attachment.htm>