Creating indexes
45 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)An index helps only the queries whose WHERE, JOIN or ORDER BY match its columns, in the right order. Creating one takes a single statement; choosing which columns, and in which order, is the real skill.
CREATE INDEX on one column
CREATE INDEX name ON table (column) builds a B-tree index on that column from the rows already in the table. MySQL then keeps it up to date on every insert, update and delete, and the optimizer can use it for queries that filter on that column.
Creating an index takes one statement, and there are three ways to write it. All of them build the same kind of index; the snippet shows them side by side.
-- 1. A separate statement (the most common)CREATE INDEX idx_shop_orders_customer ON shop_orders (customer_id);
-- 2. Inside ALTER TABLEALTER TABLE shop_orders ADD INDEX idx_shop_orders_city (city);
-- 3. Inside CREATE TABLE, for a new tableCREATE TABLE ratings ( rating_id INT PRIMARY KEY, store_id INT NOT NULL, stars TINYINT NOT NULL, INDEX idx_ratings_store (store_id));Give every index a clear name. A common pattern is idx_<table>_<columns>, such as idx_shop_orders_customer. The name appears in error messages, in SHOW INDEX and in query plans, so a good name saves time later.
Example 1 · Index the column a query filters on
SET SESSION cte_max_recursion_depth = 50000;
CREATE TABLE shop_orders ( order_id INT PRIMARY KEY, customer_id INT NOT NULL, store_id INT NOT NULL, city VARCHAR(20) NOT NULL, order_date DATE NOT NULL, amount DECIMAL(8,2) NOT NULL);
INSERT INTO shop_ordersWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)SELECT n, 1 + (n * 7) % 5000, 1 + n % 200, ELT(1 + n % 5, 'Bengaluru', 'Chennai', 'Delhi', 'Hyderabad', 'Pune'), DATE '2026-01-01' + INTERVAL (n DIV 280) DAY, 99 + (n * 13) % 900FROM seq;
SELECT COUNT(*) AS orders, SUM(amount) AS spentFROM shop_ordersWHERE customer_id = 1234;
SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;
CREATE INDEX idx_shop_orders_customer ON shop_orders (customer_id);
SELECT COUNT(*) AS orders, SUM(amount) AS spentFROM shop_ordersWHERE customer_id = 1234;
SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;+--------+---------+ | orders | spent | +--------+---------+ | 10 | 5460.00 | +--------+---------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 50000 | 1 | +---------------+---------------+ +--------+---------+ | orders | spent | +--------+---------+ | 10 | 5460.00 | +--------+---------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 10 | 1 | +---------------+---------------+
What this code does
- The generator makes 50,000 orders from 5,000 customers and 200 stores over six months;
ELT(k, ...)picks the k-th city name from the list. - "How much has customer 1234 spent?" examined all 50,000 orders without an index, and only that customer's 10 orders with
idx_shop_orders_customer. The answer is identical.
Composite indexes and the leftmost prefix rule
A composite index is one index on several columns, such as (store_id, order_date). Entries are sorted by the first column, and by the second only within equal values of the first. The leftmost prefix rule says it serves the first column, the first two, and so on.
Column order matters in a composite index. The picture shows which WHERE clauses can use an index on (store_id, order_date); the scripts below test the rule with rows examined.
Example 2 · The leftmost prefix rule
SET SESSION cte_max_recursion_depth = 50000;
CREATE TABLE shop_orders ( order_id INT PRIMARY KEY, customer_id INT NOT NULL, store_id INT NOT NULL, city VARCHAR(20) NOT NULL, order_date DATE NOT NULL, amount DECIMAL(8,2) NOT NULL);
INSERT INTO shop_ordersWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)SELECT n, 1 + (n * 7) % 5000, 1 + n % 200, ELT(1 + n % 5, 'Bengaluru', 'Chennai', 'Delhi', 'Hyderabad', 'Pune'), DATE '2026-01-01' + INTERVAL (n DIV 280) DAY, 99 + (n * 13) % 900FROM seq;
CREATE INDEX idx_shop_orders_store_date ON shop_orders (store_id, order_date);
-- First column onlySELECT SUM(amount) AS sales FROM shop_ordersWHERE store_id = 7;SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;
-- First and second columnsSELECT SUM(amount) AS sales FROM shop_ordersWHERE store_id = 7 AND order_date BETWEEN '2026-03-01' AND '2026-03-31';SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;
-- Second column onlySELECT SUM(amount) AS sales FROM shop_ordersWHERE order_date BETWEEN '2026-03-01' AND '2026-03-31';SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;+-----------+ | sales | +-----------+ | 144750.00 | +-----------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 250 | 1 | +---------------+---------------+ +----------+ | sales | +----------+ | 24811.00 | +----------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 43 | 1 | +---------------+---------------+ +------------+ | sales | +------------+ | 4768700.00 | +------------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 50000 | 1 | +---------------+---------------+
What this code does
- With
store_idalone, MySQL jumps to store 7's 250 entries. With both columns it narrows further, inside store 7, to the 43 March orders. - With
order_datealone the index is no help: March dates are spread across all 200 stores' sections of the index, so MySQL scanned all 50,000 rows. That query needs an index that starts withorder_date.
Example 3 · Column order: equality first, range last
SET SESSION cte_max_recursion_depth = 50000;
CREATE TABLE shop_orders ( order_id INT PRIMARY KEY, customer_id INT NOT NULL, store_id INT NOT NULL, city VARCHAR(20) NOT NULL, order_date DATE NOT NULL, amount DECIMAL(8,2) NOT NULL);
INSERT INTO shop_ordersWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)SELECT n, 1 + (n * 7) % 5000, 1 + n % 200, ELT(1 + n % 5, 'Bengaluru', 'Chennai', 'Delhi', 'Hyderabad', 'Pune'), DATE '2026-01-01' + INTERVAL (n DIV 280) DAY, 99 + (n * 13) % 900FROM seq;
-- Range column first: a poor order for this queryCREATE INDEX idx_date_store ON shop_orders (order_date, store_id);
SELECT COUNT(*) AS march_orders FROM shop_ordersWHERE store_id = 7 AND order_date BETWEEN '2026-03-01' AND '2026-03-31';SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;
DROP INDEX idx_date_store ON shop_orders;
-- Equality column first: the right orderCREATE INDEX idx_store_date ON shop_orders (store_id, order_date);
SELECT COUNT(*) AS march_orders FROM shop_ordersWHERE store_id = 7 AND order_date BETWEEN '2026-03-01' AND '2026-03-31';SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;+--------------+ | march_orders | +--------------+ | 43 | +--------------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 8401 | 1 | +---------------+---------------+ +--------------+ | march_orders | +--------------+ | 43 | +--------------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 43 | 1 | +---------------+---------------+
What this code does
- Both indexes contain both columns, but
(order_date, store_id)must read every March entry of every store, 8,401 of them, and check the store on each one. (store_id, order_date)jumps to store 7 and reads only its March entries, which sit side by side: 43 examined for 43 counted. Same columns, different order, 195 times less work.
The leftmost prefix rule follows from that order: MySQL can use the index for the first column alone, or the first and second together, and so on from the left. It cannot use it well for the second column alone, because those values are scattered across the whole index, just as a phone book cannot find everyone called "Priya" without reading every page. This gives the main rule for column order: put the columns compared with = first, and the column used for a range (BETWEEN, <, >) last. After a range, the next columns can no longer be used to narrow the search.
One composite index often replaces two single-column indexes. The order of conditions in WHERE does not matter; the order of columns in the index does.
Unique indexes
A unique index is an index that also refuses duplicate values. It speeds up lookups and guarantees a rule such as "one UPI ID belongs to one account". A UNIQUE constraint in CREATE TABLE creates exactly this kind of index.
A unique index is checked inside the database, so the rule holds even when two requests race. The script below creates one and shows the error that a duplicate value causes.
Example 4 · A unique index refuses duplicates
CREATE TABLE upi_accounts ( account_id INT AUTO_INCREMENT PRIMARY KEY, holder VARCHAR(40) NOT NULL, upi_id VARCHAR(40) NOT NULL, mobile VARCHAR(10) NULL);
CREATE UNIQUE INDEX uq_upi_accounts_upi_id ON upi_accounts (upi_id);CREATE UNIQUE INDEX uq_upi_accounts_mobile ON upi_accounts (mobile);
INSERT INTO upi_accounts (holder, upi_id, mobile) VALUES ('Asha Rao', 'asha@oksbi', '9876500001'), ('Ravi Kumar', 'ravi@okicici', NULL), ('Meena Iyer', 'meena@okaxis', NULL);
SELECT account_id, holder, upi_id, mobileFROM upi_accountsORDER BY account_id;
INSERT INTO upi_accounts (holder, upi_id, mobile)VALUES ('Fake Asha', 'asha@oksbi', '9000000000');+------------+------------+--------------+------------+ | account_id | holder | upi_id | mobile | +------------+------------+--------------+------------+ | 1 | Asha Rao | asha@oksbi | 9876500001 | | 2 | Ravi Kumar | ravi@okicici | NULL | | 3 | Meena Iyer | meena@okaxis | NULL | +------------+------------+--------------+------------+ ERROR 1062 (23000): Duplicate entry 'asha@oksbi' for key 'upi_accounts.uq_upi_accounts_upi_id'
What this code does
- Two accounts with
NULLmobile numbers are allowed:NULLmeans "unknown", and one unknown is never equal to another. - The second
asha@oksbifails with error 1062. The message names the key astable.index, which is why clear index names such asuq_upi_accounts_upi_idpay off.
Why not just check for duplicates in the app before inserting? Two requests can check at the same moment, both find nothing, and both insert. A unique index is checked inside the database, so the second insert always fails with error 1062, whatever the app does.
Two details matter. NULL is not equal to anything, so a unique column may hold many NULLs. And you cannot create a unique index on data that already has duplicates: clean the data first.
Fix the error
Fix the index statement
The statement fails. Pick the line that fixes it.
Round 1 of 2
Two customers live in Chennai. Which line creates the index?
Error
ERROR 1062 (23000): Duplicate entry 'Chennai' for key 'customers.idx_customers_city'
SHOW INDEX and dropping an index
SHOW INDEX FROM table lists every index of a table, one row per column of each index. DROP INDEX name ON table removes one that no query uses, because each index costs space and slows every write.
Check what exists before you add or drop an index. The first script lists the indexes of the ShopLite tables; the second drops indexes and shows the errors you may meet.
Example 5 · SHOW INDEX on ShopLite tables
SHOW INDEX FROM order_items;
SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, NON_UNIQUEFROM information_schema.STATISTICSWHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'order_items'ORDER BY INDEX_NAME, SEQ_IN_INDEX;+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | order_items | 0 | PRIMARY | 1 | order_id | A | 15 | NULL | NULL | | BTREE | | | YES | NULL | | order_items | 0 | PRIMARY | 2 | product_id | A | 25 | NULL | NULL | | BTREE | | | YES | NULL | | order_items | 1 | product_id | 1 | product_id | A | 15 | NULL | NULL | | BTREE | | | YES | NULL | +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ +------------+--------------+-------------+------------+ | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | NON_UNIQUE | +------------+--------------+-------------+------------+ | PRIMARY | 1 | order_id | 0 | | PRIMARY | 2 | product_id | 0 | | product_id | 1 | product_id | 1 | +------------+--------------+-------------+------------+
What this code does
SHOW INDEXprints one row per column of each index.PRIMARYhas two rows,order_idat position 1 andproduct_idat position 2, because the primary key is composite.Cardinalityis an estimate of distinct values and may differ on your computer, which is why this is a sample.- The
product_idindex was created automatically for the foreign key: a foreign key needs an index that starts with its column. Theinformation_schema.STATISTICSquery shows the same facts with only the columns you choose.
Example 6 · Dropping indexes and the errors you meet
CREATE INDEX idx_products_price ON products (price);
SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, NON_UNIQUEFROM information_schema.STATISTICSWHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products'ORDER BY INDEX_NAME, SEQ_IN_INDEX;
DROP INDEX idx_products_price ON products;
SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, NON_UNIQUEFROM information_schema.STATISTICSWHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products'ORDER BY INDEX_NAME, SEQ_IN_INDEX;
CREATE INDEX idx_customers_city ON customers (city);CREATE INDEX idx_customers_city ON customers (city, joined_on);+--------------------+--------------+-------------+------------+ | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | NON_UNIQUE | +--------------------+--------------+-------------+------------+ | category_id | 1 | category_id | 1 | | idx_products_price | 1 | price | 1 | | PRIMARY | 1 | product_id | 0 | +--------------------+--------------+-------------+------------+ +-------------+--------------+-------------+------------+ | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | NON_UNIQUE | +-------------+--------------+-------------+------------+ | category_id | 1 | category_id | 1 | | PRIMARY | 1 | product_id | 0 | +-------------+--------------+-------------+------------+ ERROR 1061 (42000): Duplicate key name 'idx_customers_city'
What this code does
DROP INDEXremovesidx_products_price; the second listing no longer has it.category_idstays: the foreign key needs it.- Index names must be unique within a table, so creating a second
idx_customers_cityfails with error 1061. Pick a new name such asidx_customers_city_joined, or drop the old index first.
The columns to read first are Key_name (the index name), Seq_in_index (the column's position in a composite index), Column_name and Non_unique (0 means unique). The same facts live in information_schema.STATISTICS, which you can filter and sort with an ordinary SELECT. You cannot drop the index a foreign key depends on, and the error says so.
SHOW INDEX FROM orders;lists indexes;PRIMARYis the clustered primary key.DROP INDEX idx_name ON orders;orALTER TABLE orders DROP INDEX idx_name;removes one.ALTER TABLE orders ALTER INDEX idx_name INVISIBLE;(MySQL 8.0) hides an index from the optimizer without dropping it, so you can test the effect and undo it withVISIBLE.
Next step
Check your understanding
11 quick questions