Difference between Key, Primary Key, Unique Key and Index in MySQL

by

KEY and INDEX are synonyms in MySQL. They mean the same thing. In databases you would use indexes to improve the speed of data retrieval. An index is typically created on columns used in JOIN, WHERE, and ORDER BY clauses.

Imagine you have a table called users and you want to search for all the users which have the last name ‘Smith’. Without an index, the database would have to go through all the records of the table: this is slow, because the more records you have in your database, the more work it has to do to find the result. On the other hand, an index will help the database skip quickly to the relevant pages where the ‘Smith’ records are held. This is very similar to how we, humans, go through a phone book directory to find someone by the last name: We don’t start searching through the directory from cover to cover, as long we inserted the information in some order that we can use to skip quickly to the ‘S’ pages.

Primary keys and unique keys are similar. A primary key is a column, or a combination of columns, that can uniquely identify a row. It is a special case of unique key. A table can have at most one primary key, but more than one unique key. When you specify a unique key on a column, no two distinct rows in a table can have the same value.

Also note that columns defined as primary keys or unique keys are automatically indexed in MySQL.

More Related Contents:

  1. difference between primary key and unique key
  2. MySQL – Meaning of “PRIMARY KEY”, “UNIQUE KEY” and “KEY” when used together while creating a table
  3. How do I add indexes to MySQL tables?
  4. Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?
  5. Is there a REAL performance difference between INT and VARCHAR primary keys?
  6. Insert auto increment primary key to existing table
  7. Is the primary key automatically indexed in MySQL?
  8. MySQL indexes – what are the best practices?
  9. INSERT … ON DUPLICATE KEY (do nothing)
  10. How to see indexes for a database or table in MySQL?
  11. Speeding up row counting in MySQL
  12. DOs and DONTs for Indexes [closed]
  13. MySQL performance: multiple tables vs. index on single table and partitions
  14. Mysql covering vs composite vs column index
  15. MySQL: #1075 – Incorrect table definition; autoincrement vs another key?
  16. Is there any performance gain in indexing a boolean field?
  17. Does Order of Fields of Multi-Column Index in MySQL Matter
  18. Create an index on a huge MySQL production table without table locking
  19. MySQL InnoDB: autoincrement non-primary key
  20. MySQL: ALTER IGNORE TABLE ADD UNIQUE, what will be truncated?
  21. Strings as Primary Keys in MYSQL Database [closed]
  22. MySQL Integer vs DateTime index
  23. Can MySQL use multiple indexes for a single query?
  24. How to fix MySql: index column size too large (Laravel migrate)
  25. MySQL optimizing INSERT speed being slowed down because of indices
  26. Would it help to add index to BIGINT column in MySQL?
  27. MySQL: how to index an “OR” clause
  28. Mysql performance on 6 million row table
  29. Composite Primary Key performance drawback in MySQL
  30. make an ID in a mysql table auto_increment (after the fact)

Leave a Comment