Based on my reply below, do you recommend I continue to have these indexes ?
---------- Forwarded message ---------- From: Tompkins Neil <neil.tompk...@googlemail.com> Date: Wed, Oct 13, 2010 at 8:22 PM Subject: Re: Primary key not unique on InnoDB table To: Travis Ard <travis_...@hotmail.com> Cc: "[MySQL]" <mysql@lists.mysql.com> Hi Travis, Thanks for your response. The fields which have indexes on, can be used on every other search, which is why I thought about creating them. Would you recommend against this ? Cheers Neil On Wed, Oct 13, 2010 at 6:48 PM, Travis Ard <travis_...@hotmail.com> wrote: > I couldn't help but notice you have individual indexes on nearly all the > fields of your table. If you won't be using these fields exclusively as a > join or filter condition in a query, you are unlikely to benefit from the > extra indexes and, in fact, they could slow down your inserts and add to > your storage requirements. > > -Travis > > -----Original Message----- > From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] > Sent: Wednesday, October 13, 2010 8:37 AM > To: [MySQL] > Subject: Primary key not unique on InnoDB table > > I've the following table. But why isn't the primary key unique, e.g. > preventing duplicates if entered ? > > CREATE TABLE `players_master` ( > > `players_id` bigint(20) NOT NULL AUTO_INCREMENT, > `default_teams_id` bigint(20) NOT NULL, > `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, > `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, > `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, > `dob` date NOT NULL, > `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, > `retirement_date` date DEFAULT NULL, > `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, > `estimated_value` double NOT NULL DEFAULT '0', > `contract_wage` double NOT NULL DEFAULT '0', > `rating` int(11) NOT NULL, > PRIMARY KEY (`players_id`,`default_teams_id`), > KEY `FK_players_master_countries_id` (`countries_id`), > KEY `FK_players_master_positions_id` (`positions_id`), > KEY `IDX_first_name` (`first_name`), > KEY `IDX_known_as` (`known_as`), > KEY `IDX_second_name` (`second_name`), > KEY `IDX_dob` (`dob`), > KEY `IDX_estimated_value` (`estimated_value`), > KEY `IDX_contract_wage` (`contract_wage`), > KEY `IDX_rating` (`rating`), > KEY `FK_players_master_teams_id` (`default_teams_id`), > CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) > REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO > ACTION, > CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) > REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO > ACTION, > CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) > REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO > ACTION > ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 > COLLATE=utf8_unicode_ci > > I'm confused, I thought primary keys were always unique ? > > Cheers > Neil > >
