Based on my reply below, do you recommend I continue to have these indexes ?
---------- Forwarded message ----------
From: Tompkins Neil <neil.tompkins@stripped>
Date: Wed, Oct 13, 2010 at 8:22 PM
Subject: Re: Primary key not unique on InnoDB table
To: Travis Ard <travis_ard@stripped>
Cc: "[MySQL]" <mysql@stripped>
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_ard@stripped> 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.tompkins@stripped]
> 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
>
>
