Database key issue in nuke project tables

Involved Joined: Apr 06, 2008 Posts: 484

just looking at the db structure for a module Im fixing and came across this

Code:




DROP TABLE IF EXISTS `rn_nsnpj_members`;


CREATE TABLE `rn_nsnpj_members` (


  `member_id` int(11) NOT NULL AUTO_INCREMENT,


  `member_name` varchar(255) NOT NULL DEFAULT '',


  `member_email` varchar(255) NOT NULL DEFAULT '',


  PRIMARY KEY (`member_id`),


  KEY `member_id` (`member_id`)


) ENGINE=MyISAM DEFAULT CHARSET=utf8;





DROP TABLE IF EXISTS `rn_nsnpj_members_positions`;


CREATE TABLE `rn_nsnpj_members_positions` (


  `position_id` int(11) NOT NULL AUTO_INCREMENT,


  `position_name` varchar(255) NOT NULL DEFAULT '',


  `position_weight` int(11) NOT NULL DEFAULT '0',


  PRIMARY KEY (`position_id`),


  KEY `position_id` (`position_id`)


) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

I assume the keys are incorrect.....should they not be member_name, position_name respectively

registered · Posted: Tue Nov 24, 2009 7:14 am

No, those are proper for the Primary Keys since most of the DB access and joins are going to be through the PK.

Posted: Tue Nov 24, 2009 4:42 pm

Just to make sure we are on the same page I was talking about the following

Code:




KEY `position_id` (`position_id`)

It seems to be a double up.

Posted: Wed Nov 25, 2009 8:41 am

Ooppss.. yeah, you are right. Sorry about that. I don't think it will cause an issue, but the additional index is not needed. Thanks!

Posted: Wed Nov 25, 2009 5:02 pm

I didn't check the other nuke project tables so there could be others

Posted: Fri Nov 27, 2009 3:34 pm

I have checked through the entire database and I found redundant indexes on only the following three tables:

nuke_newpages - pid / pid
nuke_nsnpj_members - member_id / member_id
nuke_nsnpj_members_positions - position_id / position_id

I will be removing these from the core fresh install SQL. Not sure yet whether it is worth removing as a part of the rndb_upgrade.php script... we'll see.