OTRS very slow on ticket closing

[fly-away]

ArticleStorage is FS and Ticket::IndexModule is a StaticDB.

mysql-slow.log:

Code: Select all

# Time: 110208  3:08:46
# User@Host: otrs[otrs] @ localhost []
# Query_time: 22.011962  Lock_time: 0.000098 Rows_sent: 0  Rows_examined: 18266220

use otrs;
SET timestamp=1297123726;
UPDATE ticket SET ticket_state_id = '19',  change_time = current_timestamp, change_by = '1' , ticket_lock_id = 1 WHERE id IN (SELECT target_key FROM link_relation WHERE source_key = '16066');

22 seconds for this query!
How can i fix this?

[Wolfgangf]

you could try the mysqltuner http://blog.mysqltuner.com script
and see how your mysql performs

[ferrosti]

Have a look at your 'Rows examined' value!
Is there an index missing on 'link_relation.source_key' ?

Please update your signature about system information!

[fly-away]

Have a look at your 'Rows examined' value!
Is there an index missing on 'link_relation.source_key' ?

Please update your signature about system information!

Its orts 2.4.7.

Code: Select all

CREATE TABLE `link_relation` (
  `source_object_id` smallint(6) NOT NULL,
  `source_key` varchar(50) NOT NULL,
  `target_object_id` smallint(6) NOT NULL,
  `target_key` varchar(50) NOT NULL,
  `type_id` smallint(6) NOT NULL,
  `state_id` smallint(6) NOT NULL,
  `create_time` datetime NOT NULL,
  `create_by` int(11) NOT NULL,
  UNIQUE KEY `link_relation_view` (`source_object_id`,`source_key`,`target_object_id`,`target_key`,`type_id`),
  KEY `FK_link_relation_target_object_id_id` (`target_object_id`),
  KEY `FK_link_relation_state_id_id` (`state_id`),
  KEY `FK_link_relation_type_id_id` (`type_id`),
  KEY `FK_link_relation_create_by_id` (`create_by`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

ticket | MyISAM | 2.83 Mb | 13774 rows
link_relation | MyISAM | 0.05 Mb | 1334 rows

13774*1334=18374516.
How can I change query or table indexes to avoid this?

[ferrosti]

I somehow cannot believe this, since mysql does not even seem to use its index on 'ticket.id'.

Please run this mySQL command and post its output

Code: Select all

EXPLAIN
SELECT ticket_state_id, change_time, change_by, ticket_lock_id FROM ticket
WHERE id IN
(SELECT target_key FROM link_relation WHERE source_key = '16066');