Jump to content

Recommended Posts

Dear all,

 

I am trying to dig into the exact difference between key and index in MySQL tables. Some say they are synonymous, others give some differences. I still don't feel confident enough to decide when to use each on a table. I would appreciate if someone well-versed in their difference explain such difference and when to use each. Thanks.

Link to post
Share on other sites

An index is generally an integer column in a table where each integer value is unique and can be used to identify that row. Since a primary key must uniquely identify each row a surrogate index is often created and used as a primary key. A primary key can be anything that uniquely identifies each row but an index is a convenient solution. For the gory details of surrogate keys, primary keys, foreign keys, etc... you should read more about relational database theory.

 

https://en.wikipedia.org/wiki/Unique_key

 

Perhaps I am not being completely accurate in my above semantics. You can also apply an "Index" to important columns in a table to speed up searches related to those columns. This is a different sort of "index" than what I was thinking of above. It will accelerate searches but will slow writes to the table. See...

 

http://www.w3schools.com/sql/sql_create_index.asp

Link to post
Share on other sites

For terminology, an index is probably more generic than a key. A key would be some kind of unique index (whether it's primary or just unique, or a pointer to another table like a foreign key), but indexes don't have to be unique. You can create an index on any number of columns in a table and it doesn't have to be (but can be) unique. Indexes are generally used to speed up searching, but they will slow down inserting, updating, and deleting. That's why you need to pick indexes carefully instead of just defining every possible index on a table. If you're not using an index for searching there's no reason to have it.

Link to post
Share on other sites

If you're looking at the structure of an InnoDB table you can click on the "Relation View" link to define foreign keys. In that case the only columns you can reference with a foreign key have to be defined as an index on their own tables. Only the InnoDB engine supports foreign keys in MySQL (I think), the MyISAM engine does not.

 

Actually, if you expand the list of indexes for a table, one of the columns is called "Keyname". It will be set to "PRIMARY" for the primary key, or else you can set the name when you create the index.

 

So yeah, it treats the two terms as the same thing more or less.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...