Member-only story
Differences between INDEX, PRIMARY, UNIQUE, and KEY in MySQL?
- INDEX and KEY are the same
An index allows duplicate values and NULL values in a column. It uses a B-Tree to manage the index. The B-tree data structure lets the index quickly find a specific value, a set of values, or a range of values, corresponding to operators such as =, >, ≤, BETWEEN, IN, and so on, in a WHERE clause.
Since B-Tree is secondary storage-friendly, the index is stored in files. The downside of using an index is that every write (insert, delete, update) to the table needs an update in the index. So too many indexes can slow down the DB.
KEY `index_created_at` (`created_at`)
On select query, the result is a set of all (including duplicates) rows that match the column value.
2. PRIMARY
A non-NULL unique value key. It can be a combination of many columns.
PRIMARY KEY (`_id`),
3. UNIQUE
Similar to primary key, but allows NULL values. No duplicate values allowed.
UNIQUE KEY `unique_key_id` (`customer_id`),
