Introduction
Indexes and Keys in MySQL are used to improve data search performance and ensure data integrity. While indexes speed up queries, keys enforce rules like uniqueness and relationships between tables.
- PRIMARY KEY – Uniquely Identify Each Row:- A primary key uniquely identifies each row and cannot be NULL. A table can have only one primary key.
Syntax:-
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
PRIMARY KEY (column_name)
);
Example:
CREATE TABLE users (
user_id INT PRIMARY KEY,
name VARCHAR(100)
);
- UNIQUE – Enforce Uniqueness (Without Being Primary):- Ensures that values in a column (or group of columns) are not duplicated, but can allow NULLs.
Syntax:-
CREATE TABLE table_name (
column1 datatype,
column2 datatype UNIQUE );
Example:
CREATE TABLE employees (
id INT,
email VARCHAR(100) UNIQUE
);
- FOREIGN KEY – Link Between Tables:- A foreign key connects a column in one table to the primary key in another. It enforces referential integrity.
Syntax:
CREATE TABLE child_table (
column datatype,
FOREIGN KEY (column) REFERENCES parent_table(parent_column)
);
Example:
CREATE TABLE orders (
order_id INT,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES users(user_id)
);
- INDEX – Speed Up Searches:- Indexes help MySQL find rows faster, especially on large datasets. Unlike keys, indexes do not enforce rules.
Syntax:-
CREATE INDEX index_name ON table_name (column_name);
Example:
CREATE INDEX idx_name ON users(name);
- COMPOSITE KEY – Combine Multiple Columns as Key:- A composite key uses two or more columns together to uniquely identify a row.
Syntax:-
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
PRIMARY KEY (column1, column2)
);
Example:
CREATE TABLE enrollments (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id)
);
- AUTO_INCREMENT (Recap) – Automatically Number Rows:- Used with a key (usually primary) to generate unique numbers for each new row automatically.
Syntax:-
CREATE TABLE table_name (
column_name INT AUTO_INCREMENT,
PRIMARY KEY (column_name)
);
Example:
CREATE TABLE books (
book_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200)
);
- FULLTEXT INDEX – Fast Text Searching:- Used for searching large text content like articles or product descriptions with support for advanced search.
Syntax:-
CREATE TABLE table_name (
column_name TEXT,
FULLTEXT (column_name)
);
Example:
CREATE TABLE posts (
id INT,
content TEXT,
FULLTEXT (content)
);
- SPATIAL INDEX – For Geographic Data:- Used with geometry-based data types (like points or shapes) to index spatial values.
Syntax:-
CREATE TABLE table_name (
column_name GEOMETRY,
SPATIAL INDEX (column_name)
);
Example:
CREATE TABLE places (
id INT,
location POINT,
SPATIAL INDEX(location)
);
- DROP INDEX – Remove an Index:- You can delete any index you created if it’s no longer needed or optimized.
Syntax:-
DROP INDEX index_name ON table_name;
Example:
DROP INDEX idx_name ON users;