Primary Key and index

Primary Key and index

Page 1 of 1

1 Replies - 1378 Views - Last Post: 04 January 2008 - 10:00 AM Rate Topic: -----

#1 maheswaran   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 19-April 07

Primary Key and index

Posted 04 January 2008 - 03:45 AM

Hi Guys,

I have doubt in primay and index key.

Bcase i have an error in my table/phpmyadmin.

PRIMARY and INDEX keys should not both be set for column `groupid`

AM wondering, realy do not about this...Pls any give brief explain

see my image

Attached File(s)

  • Attached File  error.bmp (987.55K)
    Number of downloads: 101

Is This A Good Question/Topic? 0
  • +

Replies To: Primary Key and index

#2 Martyr2   User is offline

  • Programming Theoretician
  • member icon

Reputation: 5239
  • View blog
  • Posts: 14,035
  • Joined: 18-April 07

Re: Primary Key and index

Posted 04 January 2008 - 10:00 AM

In most database setups the primary key is already indexed. That is because a primary key is unique to the record and makes for a great index. So take off the index and leave it as primary and it will be utilized by the system as a index already. You don't want to specify both because the system doesn't want to keep track of two indexes on the same field which then may conflict sometime down the road, leading to problems.

Tips on picking index fields...

1) Primary keys are usually indexed already, so don't pick these.
2) Pick a field which has enough variation in field data for uniqueness. If you have 100 records and 50 of them are the same value and the other 50 are the same of another value, there is not enough variation to make an index useful.
3) Most of the time integer fields which have a wide range of values and is distributed evenly across that range are perfect for indexing.
4) Keep indexing to a minimum. Most tables only need 1 or 2 indexes in addition to the primary key. Adding too many can even slow down your tables.
5) Keep in mind that indexing can slow down some SQL operations (such as insert or update) since indexes will have to be maintained. So really watch your indexes on high traffic tables and tables which are heavily modified regularly.

Hope these tips help you out. Enjoy!

"At DIC we be indexing masters of the universe... and code ninjas!" :snap:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1