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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 _______________________________________________ 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
