Hi Martin, I fixed the problem. For reasons unknown, the indexes were disabled. This might be caused by the script I used to backup and restore the database. After enabling them by "alter table foobar enable keys", everything works fine. Thanks! Chris On 13.09.2012 08:44, Martin Gruner wrote:
Hi Christopher,
hm, that's very strange. The query you mentioned should be fast. I guess that it could be caused by missing indexes in your database. Could you run an EXPLAIN SELECT on your query? And can you check if any indexes might be missing from the article table?
The following indexes and foreign keys should be there:
<Index Name="article_ticket_id"> <IndexColumn Name="ticket_id"/> </Index> <Index Name="article_article_type_id"> <IndexColumn Name="article_type_id"/> </Index> <Index Name="article_article_sender_type_id"> <IndexColumn Name="article_sender_type_id"/> </Index> <Index Name="article_message_id"> <IndexColumn Name="a_message_id" Size="255"/> </Index> <ForeignKey ForeignTable="valid"> <Reference Local="valid_id" Foreign="id"/> </ForeignKey> <ForeignKey ForeignTable="ticket"> <Reference Local="ticket_id" Foreign="id"/> </ForeignKey> <ForeignKey ForeignTable="article_type"> <Reference Local="article_type_id" Foreign="id"/> </ForeignKey> <ForeignKey ForeignTable="article_sender_type"> <Reference Local="article_sender_type_id" Foreign="id"/> </ForeignKey> <ForeignKey ForeignTable="users"> <Reference Local="create_by" Foreign="id"/> <Reference Local="change_by" Foreign="id"/> </ForeignKey>
Regards, mg
Am 11.09.12 10:45, schrieb Christopher Reemts:
hello everyone,
I'm trying to upgrade OTRS from version 2.2.7 to 3.1.10 while migrating the system to another server. I first "cloned" the 2.2.7 system on the new server and successfully imported the database and all configurations. then I upgraded the OTRS: 2.2 -> 2.3. -> 2.4. -> 3.0 -> 3.1. I executed all migration scripts and followed every instruction mentioned in the documentation. the system works well, except some very slow mysql queries when I try to view tickets. I activated mysql-slow-query-log, example (viewing a ticket):
------------------- SET timestamp=1347279327; SELECT sa.ticket_id, sa.a_from, sa.a_to, sa.a_cc, sa.a_subject, sa.a_reply_to, sa.a_message_id, sa.a_in_reply_to, sa.a_references, sa.a_body, st.create_time_unix, st.ticket_state_id, st.queue_id, sa.create_time, sa.a_content_type, sa.create_by, st.tn, article_sender_type_id, st.customer_id, st.until_time, st.ticket_priority_id, st.customer_user_id, st.user_id, st.responsible_user_id, sa.article_type_id, sa.a_freekey1, sa.a_freetext1, sa.a_freekey2, sa.a_freetext2, sa.a_freekey3, sa.a_freetext3, st.ticket_answered, sa.incoming_time, sa.id, st.freekey1, st.freetext1, st.freekey2, st.freetext2, st.freekey3, st.freetext3, st.freekey4, st.freetext4, st.freekey5, st.freetext5, st.freekey6, st.freetext6, st.freekey7, st.freetext7, st.freekey8, st.freetext8, st.freekey9, st.freetext9, st.freekey10, st.freetext10, st.freekey11, st.freetext11, st.freekey12, st.freetext12, st.freekey13, st.freetext13, st.freekey14, st.freetext14, st.freekey15, st.freetext15, st.freekey16, st.freetext16, st.ticket_lock_id, st.title, st.escalation_update_time, st.freetime1 , st.freetime2, st.freetime3, st.freetime4, st.freetime5, st.freetime6, st.type_id, st.service_id, st.sla_id, st.escalation_response_time, st.escalation_solution_time, st.escalation_time, st.change_time FROM article sa, ticket st WHERE sa.ticket_id = '28373' AND sa.ticket_id = st.id ORDER BY sa.create_time, sa.id ASC; # Time: 120910 14:15:35 # User@Host: otrs[otrs] @ localhost [] # Query_time: 8.183378 Lock_time: 0.000041 Rows_sent: 3 Rows_examined: 61222 ---------------------
the system takes about 5-30 seconds to view a ticket. sometimes it just works perfectly for a while, the next moment it's slow again.
table "article" has about 68.000 records table "ticket" has about 28.000 records
if you need to know other data or log files, let me know.
thanks in advance,
chris