Bug #27449 log_name column within logs table lacks an index, making filtering slow

• Submitted: 26 Mar 2007 16:14
• Modified: 10 Jul 2007 8:58
• Reporter: Mark Leith
• Status: Closed
• Category: MySQL Enterprise Monitor: Server
• Severity: S3 (Non-critical)
• Version: 1.1.0
• OS: Any (All)
• Assigned to: Darren Oldag
• CPU Architecture: Any

[26 Mar 2007 16:14] Mark Leith

Description:
The log_name column within the logs table lacks an index, making any query that filters by log name extremley slow. 

mysql> select * from logs where log_name = 'DataCollection';
...
42 rows in set (17.61 sec)

mysql> alter table logs add index log_name_idx (log_name);
Query OK, 517714 rows affected (5 min 21.47 sec)

mysql> select * from logs where log_name = 'DataCollection';
...
rows in set (0.05 sec)

How to repeat:
As above

Suggested fix:
ALTER TABLE logs ADD INDEX (log_name);

[10 Apr 2007 13:42] Darren Oldag

fixed in trunk

[10 Jul 2007 8:58] Mark Leith

Verified fixed in 1.2.0.6501

mysql> show create table logs\G
*************************** 1. row ***************************
       Table: logs
Create Table: CREATE TABLE `logs` (
  `log_id` int(11) NOT NULL auto_increment,
  `log_time` bigint(20) NOT NULL,
  `log_name` varchar(32) NOT NULL,
  `log_level` int(11) NOT NULL,
  `log_msg` text,
  PRIMARY KEY  (`log_id`),
  KEY `logs_idx` (`log_time`,`log_name`,`log_level`),
  KEY `log_name_idx` (`log_name`)
) ENGINE=InnoDB AUTO_INCREMENT=846 DEFAULT CHARSET=utf8