NYCPHP Meetup

[Anthony W]

It depends on where the join is occurring.  If you have a HABTM 
relationship between the join tables then a index should be added to the 
pivot table.  If not then I would assume that the column you are joining 
on would be a candidate for an index.  Do these tables have a PRIMARY 
KEY already established?  Take for example the following tables:


/* create our habtm tags table */
CREATE TABLE IF NOT EXISTS pictures_tags (
  picture_id int(11) NOT NULL,
  tag_id int(11) NOT NULL,
  PRIMARY KEY  (`picture_id`,`tag_id`),
  KEY `fk_tag_id` (`tag_id`),
  KEY `fk_picture_id` (`picture_id`)
);

On the pivot table I have added two foreign key constraints for look ups 
either by tag or by picture on the pivot table.  With an average load of 
ten pictures per tag it loads the picture set of ten tags in about .003 
seconds.  When adding indexes I never have found a magic formula to 
always use.  Just look for the basics (PRIMARY KEY) and then move onto 
any columns that you are joining on.

Hope this helps.