Issue opening thold module

[meralias]

I have the thold module installed, however have a problem every time I try and go into the thresholds section - I was watching the query being run, and ran an explain on it:

Code: Select all

explain SELECT DISTINCT field_name  FROM data_local AS dl  INNER JOIN (SELECT DISTINCT field_name, snmp_query_id FROM host_snmp_cache) AS hsc  ON dl.snmp_query_id=hsc.snmp_query_id  WHERE dl.data_template_id=41;
+----+-------------+-----------------+-------+--------------------------------+------------------+---------+-------+---------+------------------------------+
| id | select_type | table           | type  | possible_keys                  | key              | key_len | ref   | rows    | Extra                        |
+----+-------------+-----------------+-------+--------------------------------+------------------+---------+-------+---------+------------------------------+
|  1 | PRIMARY     | <derived2>      | ALL   | NULL                           | NULL             | NULL    | NULL  |      48 | Using temporary              | 
|  1 | PRIMARY     | dl              | ref   | data_template_id,snmp_query_id | data_template_id | 3       | const |   42201 | Using where; Distinct        | 
|  2 | DERIVED     | host_snmp_cache | index | NULL                           | present          | 1       | NULL  | 7098098 | Using index; Using temporary | 
+----+-------------+-----------------+-------+--------------------------------+------------------+---------+-------+---------+------------------------------+

Is there any way this could be optimized? I'm using Cacti 0.8.8a, along with the latest version of the thold plugin. Looking at the table defintion for host_snmp_cache, I was wondering if a key/index on field_name and snmp_query_id might help.

Code: Select all

CREATE TABLE `host_snmp_cache` (
  `host_id` mediumint(8) unsigned NOT NULL default '0',
  `snmp_query_id` mediumint(8) unsigned NOT NULL default '0',
  `field_name` varchar(50) NOT NULL default '',
  `field_value` varchar(255) default NULL,
  `snmp_index` varchar(255) NOT NULL default '',
  `oid` text NOT NULL,
  `present` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`host_id`,`snmp_query_id`,`field_name`,`snmp_index`),
  KEY `host_id` (`host_id`,`field_name`),
  KEY `snmp_index` (`snmp_index`),
  KEY `field_name` (`field_name`),
  KEY `field_value` (`field_value`),
  KEY `snmp_query_id` (`snmp_query_id`),
  KEY `host_id_snmp_query_id_snmp_index` (`host_id`,`snmp_query_id`,`snmp_index`),
  KEY `host_id_snmp_query_id` (`host_id`,`snmp_query_id`),
  KEY `present` (`present`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Any advice is gratefully received.

[cigamit]

Ya, I will have to take a look at the code and see how that can be optimized. It certainly is not ideal at all.