[mysql] Difference between Key, Primary Key, Unique Key and Index in MySQL

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.

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to indexing

numpy array TypeError: only integer scalar arrays can be converted to a scalar index How to print a specific row of a pandas DataFrame? What does 'index 0 is out of bounds for axis 0 with size 0' mean? How does String.Index work in Swift Pandas KeyError: value not in index Update row values where certain condition is met in pandas Pandas split DataFrame by column value Rebuild all indexes in a Database How are iloc and loc different? pandas loc vs. iloc vs. at vs. iat?

Examples related to primary-key

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object What is Hash and Range Primary Key? Can I use VARCHAR as the PRIMARY KEY? Can a foreign key refer to a primary key in the same table? UUID max character length MySQL duplicate entry error even though there is no duplicate entry Creating composite primary key in SQL Server What are the best practices for using a GUID as a primary key, specifically regarding performance? Add primary key to existing table Create view with primary key?

Examples related to unique-key

Unique Key constraints for multiple columns in Entity Framework How add unique key to existing table (with non uniques rows) MySQL - Meaning of "PRIMARY KEY", "UNIQUE KEY" and "KEY" when used together while creating a table difference between primary key and unique key How to remove unique key from mysql table INSERT ... ON DUPLICATE KEY (do nothing) Difference between Key, Primary Key, Unique Key and Index in MySQL

Examples related to sqlperformance

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