[dev] OTRS slow/fast for different accounts

[Johan Björni]

When a user clicks on for example the admin ikon the follwing sql is run:

SELECT ar.id as ca, st.name, ti.id, ar.create_by, ti.create_time_unix,  ti.until_time, ts.name, tst.name  FROM  ticket
ti, article ar, article_sender_type st,  ticket_state ts, ticket_state_type tst  WHERE  ti.ticket_lock_id not IN ( 1, 3 )  AND  ti.user_id = 6  AND
ar.ticket_id = ti.id  AND  st.id = ar.article_sender_type_id  AND  ts.id = ti.ticket_state_id  AND  ts.type_id = tst.id  ORDER BY ar.create_time DESC
;
On my system this takes 2-4 secs for users with many processed tickets.

I changed the SQL to:

SELECT  STRAIGHT_JOIN  ar.id as ca, st.name, ti.id, ar.create_by, ti.create_time_unix,  ti.until_time, ts.name, tst.name  FROM  ticket
ti, article_sender_type st,  ticket_state ts, ticket_state_type tst, article ar  WHERE  ti.ticket_lock_id not IN ( 1, 3 )  AND  ti.user_id = 6  AND
ar.ticket_id = ti.id  AND  st.id = ar.article_sender_type_id  AND  ts.id = ti.ticket_state_id  AND  ts.type_id = tst.id  ORDER BY ar.create_time DESC;

This gives me an instant answer.

The STRAIGHT_JOIN  forces MySQL-optimzer to use the same table order as in the SQL. By letting the article table be the last table in the join the query runs fast.

Next I have to find the code that issues this query and alter it....

Is this still not developent related :)?

//Johan



-----Original Message-----
From: dev-bounces at otrs.org [mailto:dev-bounces at otrs.org]On Behalf Of
Johan Björni
Sent: den 1 juni 2007 09:50
To: Development community of OTRS.org
Subject: RE: [dev] OTRS slow/fast for different accounts


Hi Martin,
Thanks for your reply,

>Hi Johan,
>
>Johan Björni schrieb:
>
>> When I as an admin logs in everything works well and the
>> server response is almost instant. When a normal user
>> uses the system everything takes a while, some seconds
>> for the page to load.
>>
>> I tried to give me the same rights as a normal user
>> but the server is still fast.
>> It seams like the system gets slower depending on how
>> many tickets the user has processed totally. Can this
>> be true ?
>> 
>> Any hints in how to troubleshoot this problem further.  
>> 
>> The otrs version is 2.0.4 with mysql-4.1.20 as db running
>> on RHEL 4, apache.
>> The db is 332 MB 2xXeon 3,4G/2M, 1GB.
>
>It's off topic, it's not development related.

Sorry for that, I have to go through the list definitions more carefully.

>
>Anyway, here some feedback. ,-)
>
>The performance can depend on how many open ticket you have.
>For example there is a difference if you use escalation times
>and you have 100 or 10.000 open ticket in your system.
>
>So the system has to check the escalation times for every
>ticket. So it's different if you have to 100 open or to
>10.000 open ticket access. :)
>
>But it's not important how many tickets a user has processed in the past.

Interesting, one of my testusers have 0 open tickets, the whole system has 86 open tickets (as show when I search for open tickets). When the testuser clicks the admin button it takes 5-6 sec, when I click the admin button the response is direct.
Seams like I have to figure out how to get a sql-trace from mysql to see whats really going on.

Many Thanks for your input, have a nice weekend!



>
>  -Martin
>
>((otrs)) :: OTRS GmbH :: Europaring 4 :: D - 94315 Straubing
>  Fon: +49 (0) 9421 56818 0 :: Fax: +49 (0) 9421 56818 18
>    http://www.otrs.com/ :: Communication with success!
>	
>
_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev