On 2012/05/24 04:10, Yu Watanabe wrote:
> 2. Instead INDEXes and schema design must be studied. Please provide:
> SHOW CREATE TABLE
> | thold_data | CREATE TABLE `thold_data` (
> `id` int(11) NOT NULL auto_increment,
> `rra_id` int(11) NOT NULL default '0',
> `data_id` int(11) NOT NULL default '0',
> `thold_hi` varchar(100) default NULL,
> `thold_low` varchar(100) default NULL,
> `thold_fail_trigger` int(10) unsigned default NULL,
> `thold_fail_count` int(11) NOT NULL default '0',
> `thold_alert` int(1) NOT NULL default '0',
> `thold_enabled` enum('on','off') NOT NULL default 'on',
> `bl_enabled` enum('on','off') NOT NULL default 'off',
> `bl_ref_time` int(50) unsigned default NULL,
> `bl_ref_time_range` int(10) unsigned default NULL,
> `bl_pct_down` int(10) unsigned default NULL,
> `bl_pct_up` int(10) unsigned default NULL,
> `bl_fail_trigger` int(10) unsigned default NULL,
> `bl_fail_count` int(11) unsigned default NULL,
> `bl_alert` int(2) NOT NULL default '0',
> `lastread` varchar(100) default NULL,
> `oldvalue` varchar(100) NOT NULL default '',
> `repeat_alert` int(10) unsigned default NULL,
> `notify_default` enum('on','off') default NULL,
> `notify_extra` varchar(255) default NULL,
> `host_id` int(10) default NULL,
> `syslog_priority` int(2) default '3',
> `cdef` int(11) NOT NULL default '0',
> PRIMARY KEY (`id`),
> KEY `rra_id` (`rra_id`)
> ) ENGINE=MyISAM AUTO_INCREMENT=69641 DEFAULT CHARSET=latin1 |
> EXPLAIN SELECT
> I have seen the following select query in the slow query log.
> I also saw update queries as well.
>
> mysql> explain select * from thold_data where thold_enabled='on' AND data_id
> = 91633;
> +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
> | id | select_type | table | type | possible_keys | key | key_len | ref
> | rows | Extra |
> +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
> | 1 | SIMPLE | thold_data | ALL | NULL | NULL | NULL |
> NULL | 6161 | Using where |
> +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
> 1 row in set (0.06 sec)
>
> If cache size tuning is not an option ,
> do you think that following action would be an choice to faten the queries
> little bit more?
You are selecting a record based on the value of data_id and
thold_enabled, but don't have an index on either? Add an index for both.
If data_id is unique, then you would only need an index on that.
Alex
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
