List:General Discussion« Previous MessageNext Message »
From:Travis Ard Date:October 15 2010 10:47pm
Subject:RE: Primary key not unique on InnoDB table
View as plain text  
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.tompkins@stripped] 
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.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

 

 


Thread
Primary key not unique on InnoDB tableTompkins Neil13 Oct
  • Re: Primary key not unique on InnoDB tablejoao13 Oct
  • Re: Primary key not unique on InnoDB tablejoao13 Oct
    • Re: Primary key not unique on InnoDB tableTompkins Neil13 Oct
  • Re: Primary key not unique on InnoDB tableKrishna Chandra Prajapati13 Oct
    • Re: Primary key not unique on InnoDB tableTompkins Neil13 Oct
  • Re: Primary key not unique on InnoDB tablejoao13 Oct
    • Re: Primary key not unique on InnoDB tableTompkins Neil13 Oct
  • Re: Primary key not unique on InnoDB tableMySQL)13 Oct
  • RE: Primary key not unique on InnoDB tableTravis Ard13 Oct
    • Re: Primary key not unique on InnoDB tableTompkins Neil13 Oct
      • Fwd: Primary key not unique on InnoDB tableTompkins Neil15 Oct
        • RE: Primary key not unique on InnoDB tableTravis Ard16 Oct
Re: Primary key not unique on InnoDB tableMySQL)13 Oct
  • Re: Primary key not unique on InnoDB tableTompkins Neil13 Oct