You obviously know best how your application will query your database, and you may have already thought through your indexing strategy. If so, please disregard my comments.
In my experience, it is not often you need separate indexes on most or all the columns in a table (excepting very narrow tables, perhaps), so I would think about how you anticipate the database might use each of these indexes. Even though you may have multiple indexes available, most of the time a database query optimizer will only choose one when deciding how to retrieve data for a query. So, if you have a column like first_name that is indexed, your database engine may never use this index unless you have a query like "select * from players_master where first_name = 'xyz'". If a column is part of your select list, but is not used as your WHERE clause expression or as part of a table join, indexing that column may not be a benefit. Running EXPLAIN will tell you whether or not the index you anticipate is actually being used for your query. -Travis From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Friday, October 15, 2010 3:43 AM To: [MySQL]; Travis Ard Subject: Fwd: Primary key not unique on InnoDB table 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
