Jump to content

Index vs Key


j.silver

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 comment
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 comment
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 comment
Share on other sites

Thanks. I have noticed that phpMyAdmin allows index assignment, but no key, which indicates that such slight difference is not accounted for in phpMyAdmin and both index and key are treated the same.

Link to comment
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 comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...