How to Optimize MySQL Queries with Indexes (Faster)

As the amount of data in a database grows, queries that used to be fast can become slow. One of the most effective solutions to speed up queries in MySQL is using an Index. An index works like a book index: instead of reading every page one by one, you jump straight to the right page. This article covers how to create and use MySQL indexes correctly.

What Is an Index in MySQL?

An index is an additional data structure that MySQL stores to speed up search operations. Without an index, MySQL has to perform a Full Table Scan — reading every row from beginning to end. With an index, MySQL can find the data being searched for in a much shorter time. The downside is that an index requires extra storage space and slightly slows down INSERT, UPDATE, and DELETE operations.

Checking Query Performance with EXPLAIN

Before creating an index, use EXPLAIN to analyze how MySQL runs your query:

-- Example table without an index (other than the PRIMARY KEY)
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Analyze the query before adding an index
EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

-- Important columns to pay attention to:
-- type: ALL = full scan (bad), ref/range/const = uses an index (good)
-- key: name of the index used (NULL = no index used)
-- rows: estimated number of rows read
-- Extra: additional info such as "Using filesort", "Using temporary"

Creating a Basic Index (Single Column Index)

Create an index on columns that are frequently used in the WHERE clause:

-- Way 1: Create the index during CREATE TABLE
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    INDEX idx_category (category)
) ENGINE=InnoDB;

-- Way 2: Add an index to an existing table
CREATE INDEX idx_category ON products (category);

-- Way 3: Using ALTER TABLE
ALTER TABLE products ADD INDEX idx_price (price);

-- Re-check EXPLAIN after the index is created
EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

Composite Index (Multi-Column Index)

If a query frequently filters by several columns at once, use a Composite Index:

-- Create a composite index for queries that often filter category + price
CREATE INDEX idx_category_price ON products (category, price);

-- This query takes good advantage of the composite index
SELECT * FROM products
WHERE category = 'Electronics'
AND price BETWEEN 1000000 AND 5000000;

-- The "Leftmost Prefix" rule: a composite index (A, B, C)
-- can be used for: WHERE A, WHERE A AND B, WHERE A AND B AND C
-- CANNOT be used for: WHERE B alone, or WHERE C alone

UNIQUE Index

A UNIQUE Index ensures there are no duplicate values in that column, while also serving as an index to speed up searches:

-- Add an SKU column and create a unique index
ALTER TABLE products ADD COLUMN sku VARCHAR(50);

-- Create a unique index
CREATE UNIQUE INDEX idx_unique_sku ON products (sku);

-- Or during CREATE TABLE
CREATE TABLE products_v2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    UNIQUE INDEX idx_sku (sku)
) ENGINE=InnoDB;

FULLTEXT Index for Text Search

For text search such as a search feature, use a FULLTEXT Index:

-- Create an articles table with a fulltext index
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    FULLTEXT INDEX ft_title_content (title, content)
) ENGINE=InnoDB;

-- Use MATCH ... AGAINST for fulltext search
SELECT id, title,
    MATCH(title, content) AGAINST('mysql database' IN NATURAL LANGUAGE MODE) AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('mysql database' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC
LIMIT 10;

Viewing All Indexes on a Table

-- Show all indexes on the products table
SHOW INDEX FROM products;

-- Or query INFORMATION_SCHEMA
SELECT
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    NON_UNIQUE,
    SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'products'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

Deleting an Index

-- Drop an index by name
DROP INDEX idx_category ON products;

-- Or using ALTER TABLE
ALTER TABLE products DROP INDEX idx_price;

When Should You Create an Index?

• Columns frequently used in the WHERE, JOIN ON, ORDER BY, or GROUP BY clauses.
• Columns with high cardinality (many unique values), such as email or phone number.
• Foreign Key columns used for JOINs between tables.

When Should You Avoid Indexes?

• Very small tables — a Full Scan is faster than using an index.
• Columns with low cardinality such as a gender column (containing only 2 values).
• Tables that are more often INSERTed/UPDATEd/DELETEd than SELECTed.

Conclusion

An index is the primary tool for optimizing query performance in MySQL. Use EXPLAIN to analyze whether your query is already taking good advantage of indexes. Create indexes on columns that are frequently filtered or joined, and consider a composite index for multi-condition queries. But remember, an index is not a universal solution — too many indexes can actually slow down write operations. The key is to monitor slow queries and add indexes strategically.