[dev.icinga.com #3018] Redundant indexes in MySQL schema.

[icinga-migration]

This issue has been migrated from Redmine: https://dev.icinga.com/issues/3018

Created by robe on 2012-08-25 14:47:28 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2012-09-23 09:57:47 +00:00)
Target Version: 1.8
Last Update: 2014-12-08 14:37:49 +00:00 (in Redmine)

Icinga Version: 1.10.0
OS Version: any

---

Attachments

• mysql_index.patch mfriedrich - 2012-09-16 14:30:24 +00:00

Changesets

2012-09-16 15:35:05 +00:00 by mfriedrich be502d8

idoutils: fix redundant indexes in MySQL schema (thx Michael Renner) #3018 - MF

refs #3018

[icinga-migration]

Updated by robe on 2012-08-25 15:01:05 +00:00

In #1173 the default type for all PRIMARY KEY columns was changed to serial. This causes MySQL to create two indexes over the same column - one for the serial column type and one for enforcing the PRIMARY KEY.

mysql> show create table icinga_hoststatus \G
       Table: icinga_hoststatus
Create Table: CREATE TABLE `icinga_hoststatus` (
  `hoststatus_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
[..]
  PRIMARY KEY (`hoststatus_id`),
  UNIQUE KEY `hoststatus_id` (`hoststatus_id`),
[..]
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Current host status information'

mysql> show index from icinga_hoststatus \G
*************************** 1. row ***************************
        Table: icinga_hoststatus
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: hoststatus_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: icinga_hoststatus
   Non_unique: 0
     Key_name: hoststatus_id
 Seq_in_index: 1
  Column_name: hoststatus_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
[..]

This causes wasted disk space and increased IOPS when writing to the tables in question. Please revert all serial PRIMARY KEY columns to

bigint(20) unsigned NOT NULL AUTO_INCREMENT

and keep the PRIMARY KEY in place.

See http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html for SERIAL and BIGINT documentation and http://dev.mysql.com/doc/refman/5.5/en/create-table.html for the PRIMARY KEY implications.

[icinga-migration]

Updated by mfriedrich on 2012-08-26 00:08:57 +00:00

• Status changed from New to Assigned
• Assigned to set to robe

[icinga-migration]

Updated by robe on 2012-09-16 14:17:12 +00:00

• Assigned to changed from robe to mfriedrich

Patch forwarded to MichaelF

[icinga-migration]

Updated by mfriedrich on 2012-09-16 14:30:24 +00:00

• File added mysql_index.patch

[icinga-migration]

Updated by mfriedrich on 2012-09-16 15:40:05 +00:00

• Status changed from Assigned to 7
• Done % changed from 0 to 100

up in my mfriedrich/ido branch - thanks Michael for the patch.

dropping indexes might take a bit, so if users complain that the script takes too long, just tell them the url to this issue (or check what the upgrade script does).

[icinga-migration]

Updated by mfriedrich on 2012-09-23 09:57:47 +00:00

• Status changed from 7 to Resolved

[icinga-migration]

Updated by mfriedrich on 2014-12-08 14:37:49 +00:00

• Project changed from 18 to Core, Classic UI, IDOUtils
• Category changed from 112 to IDOUtils
• Icinga Version changed from 1 to 1
• OS Version set to any