MySQL - Create Index

MySQL - Creating Indexes

A database index works much like the index in the back of a book. Instead of scanning every page (every row) to find information, you can look up the topic in the index, which tells you exactly which page(s) to go to. Similarly, a database index allows the database engine to find rows with specific column values quickly, drastically improving the speed of data retrieval (queries).

Why Use Indexes?

• Faster SELECT queries: This is the primary benefit. Indexes are most effective for WHERE clauses, JOIN conditions, and ORDER BY clauses.
• Enforcing Uniqueness: A UNIQUE index ensures that no two rows can have the same value in the indexed column(s).
• Improved JOIN Performance: Indexing foreign key columns significantly speeds up join operations.

However, indexes have a cost. They consume storage space and slow down data modification operations (INSERT, UPDATE, DELETE) because the index must also be updated. Therefore, you should only create indexes that are actually used.

Types of Indexes in MySQL

MySQL supports several index types, with B-Tree being the most common:

• B-Tree: The default index type for most storage engines like InnoDB and MyISAM. It works well for a wide range of queries, including equality (=), range (>, <, BETWEEN), and prefix (LIKE 'prefix%') lookups.
• UNIQUE: A B-Tree index that enforces uniqueness on the indexed column(s). PRIMARY KEY is a special type of UNIQUE index.
• FULLTEXT: Used for performing full-text searches on character-based columns (CHAR, VARCHAR, TEXT). It allows you to search for words and phrases within text content.
• SPATIAL: Used for indexing geospatial data types, allowing for efficient queries on geographic data (e.g., finding all locations within a certain radius).

Creating an Index on a New Table

You can define indexes directly within your CREATE TABLE statement.

CREATE TABLE Products (
    id INT AUTO_INCREMENT PRIMARY KEY, -- PRIMARY KEY is automatically an index
    product_code VARCHAR(20) NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    category_id INT,
    price DECIMAL(10, 2),

    -- Creating a UNIQUE index on product_code
    UNIQUE INDEX idx_product_code (product_code),

    -- Creating a simple (B-Tree) index on category_id
    INDEX idx_category_id (category_id)
);

Creating an Index on an Existing Table

You can add an index to a table that already exists using either CREATE INDEX or ALTER TABLE.

Using CREATE INDEX

The CREATE INDEX statement is clear and explicit.

-- Creates a regular index on the product_name column
CREATE INDEX idx_product_name ON Products(product_name);

Using ALTER TABLE

The ALTER TABLE statement is more versatile and can be used for many schema modifications.

-- Creates the same index as above using ALTER TABLE
ALTER TABLE Products ADD INDEX idx_price(price);

Unique and Composite Indexes

Unique Index

As shown earlier, a UNIQUE index prevents duplicate values. Trying to insert a product_code that already exists in the Products table would result in an error.

CREATE UNIQUE INDEX idx_product_code ON Products(product_code);

Composite Index

A composite (or multi-column) index is an index on two or more columns. The order of columns in the index is very important. An index on (col1, col2) can be used for queries filtering on col1 alone, or on both col1 and col2. It is generally not used for queries filtering on col2 alone.

-- Create a composite index for lookups by category and price
CREATE INDEX idx_category_price ON Products(category_id, price);

This index would be very effective for a query like SELECT * FROM Products WHERE category_id = 5 ORDER BY price;.

How to See if an Index is Being Used

The EXPLAIN command is a powerful tool for analyzing how MySQL executes a query. It shows which indexes, if any, are used.

EXPLAIN SELECT * FROM Products WHERE product_code = 'XYZ-123';

In the output of EXPLAIN, look at the key column. If it shows the name of your index (e.g., idx_product_code), the index is being used effectively. If it’s NULL, MySQL is performing a full table scan, and you might need to add or adjust an index.

Index Best Practices

• Index Selectively: Don’t index every column. Only index columns used frequently in WHERE, JOIN, or ORDER BY clauses.
• Consider Cardinality: Indexes work best on columns with high cardinality (many unique values). Indexing a gender column with only ‘Male’, ‘Female’, ‘Other’ is often less effective than indexing a username column.
• Index Foreign Keys: Always place indexes on foreign key columns. This dramatically improves JOIN performance.
• Use Composite Indexes Wisely: For queries that filter on multiple columns, a composite index is often better than multiple single-column indexes.
• Keep Indexes Small: Use the most efficient data types for your columns to keep indexes compact and fast.

Dropping an Index

If an index is unused or no longer needed, you should remove it to save space and improve write performance. You can use DROP INDEX.

-- The index name is specified, along with the table it belongs to
DROP INDEX idx_price ON Products;