Slow query for 1.6.5

[alj]

here's a query o found in /var/log/mysql/mysql-slow.log
select distinct t.triggerid,t.expression,t.description,t.url,t.com ments,t.status,t.value,t.priority,t.type from triggers t,functions f,items i where i.status<>3 and i.itemid=f.itemid and t.status=0 and f.triggerid=t.triggerid and f.itemid=26971;

I decided to run explain on it and here's what i got (9 minutes!!!):
mysql> explain select distinct t.triggerid,t.expression,t.description,t.url,t.com ments,t.status,t.value,t.priority,t.type from triggers t,functions f,items i where i.status<>3 and i.itemid=f.itemid and t.status=0 and f.triggerid=t.triggerid and f.itemid=26971;


+----+-------------+-------+--------+-------------------------+-------------+---------+--------------------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------+-------------+---------+--------------------+------+-----------------+
| 1 | SIMPLE | i | const | PRIMARY,items_3 | PRIMARY | 8 | const | 1 | Using temporary |
| 1 | SIMPLE | f | ref | functions_1,functions_2 | functions_2 | 8 | const | 8 | |
| 1 | SIMPLE | t | eq_ref | PRIMARY,triggers_1 | PRIMARY | 8 | zabbix.f.triggerid | 1 | Using where |
+----+-------------+-------+--------+-------------------------+-------------+---------+--------------------+------+-----------------+
3 rows in set (9 min 41.54 sec)

mysql> show index from items ;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| items | 0 | PRIMARY | 1 | itemid | A | 22447 | NULL | NULL | | BTREE | |
| items | 0 | items_1 | 1 | hostid | A | 561 | NULL | NULL | | BTREE | |
| items | 0 | items_1 | 2 | key_ | A | 22447 | NULL | NULL | | BTREE | |
| items | 1 | items_2 | 1 | nextcheck | A | 11223 | NULL | NULL | | BTREE | |
| items | 1 | items_3 | 1 | status | A | 7 | NULL | NULL | | BTREE | |
| items | 1 | items_4 | 1 | templateid | A | 3741 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)
mysql> show index from functions ;
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| functions | 0 | PRIMARY | 1 | functionid | A | 7324 | NULL | NULL | | BTREE | |
| functions | 1 | functions_1 | 1 | triggerid | A | 7324 | NULL | NULL | | BTREE | |
| functions | 1 | functions_2 | 1 | itemid | A | 7324 | NULL | NULL | | BTREE | |
| functions | 1 | functions_2 | 2 | function | A | 7324 | NULL | NULL | | BTREE | |
| functions | 1 | functions_2 | 3 | parameter | A | 7324 | NULL | NULL | | BTREE | |
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)
mysql> show index from triggers ;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| triggers | 0 | PRIMARY | 1 | triggerid | A | 7979 | NULL | NULL | | BTREE | |
| triggers | 1 | triggers_1 | 1 | status | A | 2 | NULL | NULL | | BTREE | |
| triggers | 1 | triggers_2 | 1 | value | A | 2 | NULL | NULL | | BTREE | |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.01 sec)


Whats wrong with this picture? Am i missing some key?

[Alexei]

I do not see any issues with the query. The fact that the explain (not the query itself!) took more than 9 minutes is an indicator of serious performance problems of your MySQL server!

[alj]

I do not see any issues with the query. The fact that the explain (not the query itself!) took more than 9 minutes is an indicator of serious performance problems of your MySQL server!

Yep the problem was with the server, thanks.