[Building Sakai] Foreign key index duplicates

[Kusnetz, Jeremy]

I'd like to ping the community again on this.  We have been running
Sakai in our development environment with the Foreign keys removed with
no apparent ill effect.  Any thoughts on doing this?

 

From: Kusnetz, Jeremy 
Sent: Thursday, March 08, 2012 2:59 PM
To: sakai-dev
Subject: Foreign key index duplicates

 

We are using Mysql 5.1 on Sakai 2.8.1

 

I downloaded the Percona Toolkit which contains a program called
pt-duplicate-key-checker.  The analyzes all indexes in a database and
spits out duplicates with a suggestion on how to remove the duplicate
index.

 

One thing I'm noticing over and over again are duplicate Foreign Key
indexes when an existing index already existed, usually the primary key.
For example:

 

CREATE TABLE `ASN_AP_ITEM_T` (

  `ID` bigint(20) NOT NULL,

  `ASSIGNMENT_ID` varchar(255) DEFAULT NULL,

  `TITLE` varchar(255) DEFAULT NULL,

  `TEXT` text,

  `RELEASE_DATE` datetime DEFAULT NULL,

  `RETRACT_DATE` datetime DEFAULT NULL,

  `HIDE` bit(1) DEFAULT NULL,

  PRIMARY KEY (`ID`),

  KEY `FK514CEE15935EEE07` (`ID`),

  CONSTRAINT `FK514CEE15935EEE07` FOREIGN KEY (`ID`) REFERENCES
`ASN_SUP_ITEM_T` (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

Couldn't we not have the FK514CEE15935EEE07 KEY since ID is already
indexed?  If so what about this case?

 

CREATE TABLE `CM_OFFICIAL_INSTRUCTORS_T` (

  `ENROLLMENT_SET_ID` bigint(20) NOT NULL,

  `INSTRUCTOR_ID` varchar(255) DEFAULT NULL,

  UNIQUE KEY `ENROLLMENT_SET_ID` (`ENROLLMENT_SET_ID`,`INSTRUCTOR_ID`),

  KEY `FK470F8ACCC28CC1AD` (`ENROLLMENT_SET_ID`),

  CONSTRAINT `FK470F8ACCC28CC1AD` FOREIGN KEY (`ENROLLMENT_SET_ID`)
REFERENCES `CM_ENROLLMENT_SET_T` (`ENROLLMENT_SET_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

In this case it's not primary key, but there is a unique key
ENROLLMENT_SET_ID indexing ENROLLMENT_SET_ID and INSTRUCTOR_ID.  Since
ENROLLMENT_SET_ID is already being indexed isn't the key
FK470F8ACCC28CC1AD a duplicate?

 

These kinds of things are all over the place.  Is this just the case of
MySQL being dumb and adding an index when it's not needed?  Or is there
something I'm not understanding.   I would think a lot of these Foreign
Key duplicates could be removed increasing update and insert speeds.

 

This message is private and confidential. If you have received it in error, please notify the sender and remove it from your system.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20120418/ff0272d5/attachment.html