The cost of indexes on writes
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)Every index makes some reads faster and every write slower. A table with the right three indexes is fast; a table with ten "just in case" indexes is slow to change, big on disk and no faster to read. Good index design is choosing which ones earn their cost.
Every index slows INSERT, UPDATE and DELETE
Write cost is the extra work every INSERT, UPDATE and DELETE does to keep each index sorted: one entry added or removed in every index of the table. More indexes mean slower writes.
An index is a sorted copy of some columns, so every write must update it. The scripts below run the same 50,000 inserts with and without four indexes, then update an indexed column.
Example 1 · The same 50,000 inserts with and without four indexes
SET SESSION cte_max_recursion_depth = 50000;
CREATE TABLE fest_plain ( reg_id INT PRIMARY KEY, email VARCHAR(60) NOT NULL, phone VARCHAR(10) NOT NULL, college VARCHAR(40) NOT NULL, event_code CHAR(6) NOT NULL, fee INT NOT NULL);
-- Same columns, plus four secondary indexesCREATE TABLE fest_indexed LIKE fest_plain;CREATE INDEX idx_fest_email ON fest_indexed (email);CREATE INDEX idx_fest_phone ON fest_indexed (phone);CREATE INDEX idx_fest_college ON fest_indexed (college);CREATE INDEX idx_fest_event ON fest_indexed (event_code, fee);
SET @start = NOW(6);INSERT INTO fest_plainWITH RECURSIVE seq AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)SELECT n, CONCAT('student', (n * 7919) % 50000, '@college.edu'), CONCAT('9', LPAD((n * 7919) % 1000000000, 9, '0')), CONCAT('College ', n % 300), CONCAT('EV', LPAD(n % 40, 4, '0')), 100 + n % 400FROM seq;SELECT TIMESTAMPDIFF(MICROSECOND, @start, NOW(6)) DIV 1000 AS plain_ms;
SET @start = NOW(6);INSERT INTO fest_indexedWITH RECURSIVE seq AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)SELECT n, CONCAT('student', (n * 7919) % 50000, '@college.edu'), CONCAT('9', LPAD((n * 7919) % 1000000000, 9, '0')), CONCAT('College ', n % 300), CONCAT('EV', LPAD(n % 40, 4, '0')), 100 + n % 400FROM seq;SELECT TIMESTAMPDIFF(MICROSECOND, @start, NOW(6)) DIV 1000 AS indexed_ms;+----------+ | plain_ms | +----------+ | 3818 | +----------+ +------------+ | indexed_ms | +------------+ | 10105 | +------------+
What this code does
- A college fest registers 50,000 students into two tables with the same columns.
NOW(6)gives the time with microseconds, so the difference measures theINSERTin milliseconds. - The table with four secondary indexes took more than twice as long: every row went into five B-trees instead of one. Your times will differ, but the gap is normal.
Example 2 · Updating an indexed column costs more
SET SESSION cte_max_recursion_depth = 20000;
CREATE TABLE fest_plain ( reg_id INT PRIMARY KEY, email VARCHAR(60) NOT NULL, phone VARCHAR(10) NOT NULL, college VARCHAR(40) NOT NULL, event_code CHAR(6) NOT NULL, fee INT NOT NULL);
-- Same columns, plus four secondary indexesCREATE TABLE fest_indexed LIKE fest_plain;CREATE INDEX idx_fest_email ON fest_indexed (email);CREATE INDEX idx_fest_phone ON fest_indexed (phone);CREATE INDEX idx_fest_college ON fest_indexed (college);CREATE INDEX idx_fest_event ON fest_indexed (event_code, fee);
INSERT INTO fest_plainWITH RECURSIVE seq AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 20000)SELECT n, CONCAT('student', (n * 7919) % 20000, '@college.edu'), CONCAT('9', LPAD((n * 7919) % 1000000000, 9, '0')), CONCAT('College ', n % 300), CONCAT('EV', LPAD(n % 40, 4, '0')), 100 + n % 400FROM seq;INSERT INTO fest_indexedWITH RECURSIVE seq AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 20000)SELECT n, CONCAT('student', (n * 7919) % 20000, '@college.edu'), CONCAT('9', LPAD((n * 7919) % 1000000000, 9, '0')), CONCAT('College ', n % 300), CONCAT('EV', LPAD(n % 40, 4, '0')), 100 + n % 400FROM seq;
-- fee is in idx_fest_event; college has its own indexSET @start = NOW(6);UPDATE fest_indexed SET college = CONCAT(college, ' (Autonomous)');SELECT TIMESTAMPDIFF(MICROSECOND, @start, NOW(6)) DIV 1000 AS indexed_column_ms;
-- phone_verified is in no index: only the row changesALTER TABLE fest_indexed ADD COLUMN phone_verified TINYINT NOT NULL DEFAULT 0;SET @start = NOW(6);UPDATE fest_indexed SET phone_verified = 1;SELECT TIMESTAMPDIFF(MICROSECOND, @start, NOW(6)) DIV 1000 AS plain_column_ms;+-------------------+ | indexed_column_ms | +-------------------+ | 2611 | +-------------------+ +-----------------+ | plain_column_ms | +-----------------+ | 1110 | +-----------------+
What this code does
- Changing
collegeon 20,000 rows also moved 20,000 entries insideidx_fest_college: each old entry deleted and a new one inserted at its sorted place. - Changing
phone_verified, a column in no secondary index, only rewrote the rows. Same number of rows, much less work.
An INSERT adds one entry to every index of the table; a DELETE removes one from every index; an UPDATE of an indexed column removes the old entry and inserts the new one at its new sorted place.
With one primary key, an insert writes into one B-tree. With five secondary indexes, it writes into six. Each of them may need a page read from disk and, now and then, a page split. On a table that gets lakhs of inserts a day, such as UPI payments or app logs, that difference decides how much load the server can take. Updates of columns that are not in any secondary index do not touch those indexes. Updating fee is cheap; updating email on a table with an email index is not. A secondary index also stores the primary key, so changing a primary key value rewrites the entry in every index.
Predict the output
Which trees does a write touch?
orders has a primary key and one index on customer_id (made for its foreign key).
Round 1 of 3
How many B-trees does this INSERT write to?
Index size on disk
Every index takes disk space and memory. The query below reads the data and index sizes of a table, so you can see how much each index costs.
Example 3 · How much space do the indexes take?
SET SESSION cte_max_recursion_depth = 50000;
CREATE TABLE fest_plain ( reg_id INT PRIMARY KEY, email VARCHAR(60) NOT NULL, phone VARCHAR(10) NOT NULL, college VARCHAR(40) NOT NULL, event_code CHAR(6) NOT NULL, fee INT NOT NULL);
-- Same columns, plus four secondary indexesCREATE TABLE fest_indexed LIKE fest_plain;CREATE INDEX idx_fest_email ON fest_indexed (email);CREATE INDEX idx_fest_phone ON fest_indexed (phone);CREATE INDEX idx_fest_college ON fest_indexed (college);CREATE INDEX idx_fest_event ON fest_indexed (event_code, fee);
INSERT INTO fest_indexedWITH RECURSIVE seq AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)SELECT n, CONCAT('student', (n * 7919) % 50000, '@college.edu'), CONCAT('9', LPAD((n * 7919) % 1000000000, 9, '0')), CONCAT('College ', n % 300), CONCAT('EV', LPAD(n % 40, 4, '0')), 100 + n % 400FROM seq;
ANALYZE TABLE fest_indexed;
SELECT index_name, stat_value AS pages, stat_value * @@innodb_page_size DIV 1024 AS size_kbFROM mysql.innodb_index_statsWHERE database_name = DATABASE() AND table_name = 'fest_indexed' AND stat_name = 'size'ORDER BY index_name;+-----------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------------+---------+----------+----------+ | practice.fest_indexed | analyze | status | OK | +-----------------------+---------+----------+----------+ +------------------+-------+---------+ | index_name | pages | size_kb | +------------------+-------+---------+ | PRIMARY | 289 | 4624 | | idx_fest_college | 161 | 2576 | | idx_fest_email | 161 | 2576 | | idx_fest_event | 161 | 2576 | | idx_fest_phone | 97 | 1552 | +------------------+-------+---------+
What this code does
PRIMARYis the table itself, rows included. Each secondary index stores its columns plusreg_idfor all 50,000 rows.- Add up the four secondary indexes and they are bigger than the table. That is disk, backup size and buffer pool memory spent on every one of them.
An index's leaf pages hold the indexed columns plus the primary key for every row of the table, so an index on a wide VARCHAR column can be half the size of the table itself. A table with several indexes often has more bytes in its indexes than in its rows.
Size matters beyond disk cost. The buffer pool, MySQL's memory cache, holds pages of tables and indexes. Every extra index competes for that memory, so the pages your hot queries need are more often pushed out and read from disk again. Backups and restores also grow, and so does the time to add a column to the table. Two places show sizes: mysql.innodb_index_stats (pages per index, multiplied by the 16 KB page size) and information_schema.TABLES (DATA_LENGTH and INDEX_LENGTH in bytes). Both are estimates refreshed by ANALYZE TABLE.
Unused and duplicate indexes
A redundant index is a leftmost prefix of another index, such as (email) next to (email, college): any query that can use it can use the longer one. A duplicate index has the same columns; an unused index is one no query reads.
Some indexes cost without helping. The scripts below list duplicate and redundant indexes, then hide one with an invisible index to test dropping it safely.
Example 4 · Duplicate and redundant indexes
CREATE INDEX idx_customers_city ON customers (city);CREATE INDEX idx_customers_city_join ON customers (city, joined_on);CREATE INDEX idx_customers_city_copy ON customers (city);SHOW WARNINGS;
SELECT table_name, redundant_index_name, redundant_index_columns, dominant_index_name, dominant_index_columnsFROM sys.schema_redundant_indexesWHERE table_schema = DATABASE()ORDER BY table_name, redundant_index_name;+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1831 | Duplicate index 'idx_customers_city_copy' defined on the table 'practice.customers'. This is deprecated and will be disallowed in a future release. | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ +------------+-------------------------+-------------------------+-------------------------+------------------------+ | table_name | redundant_index_name | redundant_index_columns | dominant_index_name | dominant_index_columns | +------------+-------------------------+-------------------------+-------------------------+------------------------+ | customers | idx_customers_city | city | idx_customers_city_join | city,joined_on | | customers | idx_customers_city_copy | city | idx_customers_city | city | | customers | idx_customers_city_copy | city | idx_customers_city_join | city,joined_on | +------------+-------------------------+-------------------------+-------------------------+------------------------+
What this code does
- The third index is an exact copy, so MySQL creates it but returns warning 1831 ("deprecated and will be disallowed in a future release"). The mysql client only shows the warning count unless you run
SHOW WARNINGS. sys.schema_redundant_indexeslistsidx_customers_cityandidx_customers_city_copyas redundant (the copy twice, once for each index that covers it):(city, joined_on)already serves everything they could. Drop both and keep the composite one.
Example 5 · Test a removal with an invisible index
CREATE INDEX idx_orders_date ON orders (order_date);
SELECT object_name, index_nameFROM sys.schema_unused_indexesWHERE object_schema = DATABASE() AND object_name = 'orders'ORDER BY index_name;
-- Hide it from the optimizer, but keep maintaining itALTER TABLE orders ALTER INDEX idx_orders_date INVISIBLE;EXPLAIN SELECT order_id FROM orders WHERE order_date >= '2025-05-01';
-- Something got slower? One statement brings it backALTER TABLE orders ALTER INDEX idx_orders_date VISIBLE;EXPLAIN SELECT order_id FROM orders WHERE order_date >= '2025-05-01';+-------------+-----------------+ | object_name | index_name | +-------------+-----------------+ | orders | customer_id | | orders | idx_orders_date | +-------------+-----------------+ +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | orders | NULL | ALL | NULL | NULL | NULL | NULL | 15 | 33.33 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ +----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | orders | NULL | range | idx_orders_date | idx_orders_date | 3 | NULL | 4 | 100.00 | Using where; Using index | +----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
What this code does
sys.schema_unused_indexeslists indexes with no reads since the server started; on a fresh table every index appears, so the list only means something after weeks of real traffic.- While invisible,
idx_orders_datedoes not even appear inpossible_keys, so the plan is the one you would get after dropping it. Making itVISIBLEagain needs no rebuild, which is what makes this a safe test.
MySQL's sys schema, a built-in set of helper views, finds these indexes: sys.schema_redundant_indexes lists duplicate and redundant indexes with the index that makes each one unnecessary, and sys.schema_unused_indexes lists indexes with no reads since the last restart.
Be careful with "unused". A month-end report or a yearly audit query may use an index rarely but badly need it. That is why MySQL 8.0 has invisible indexes: ALTER TABLE t ALTER INDEX idx INVISIBLE hides an index from the optimizer while it is still maintained. If nothing slows down over a full business cycle, drop it; if something does, VISIBLE brings it back instantly.
Choosing indexes from real queries
A digest is the shape of a query with its values replaced by ?, so every run of the same query is counted together. Digests show which queries run most and examine the most rows.
Indexes should come from the queries the application actually runs. The query below asks performance_schema which statements examined the most rows.
Example 6 · Find the queries that need an index
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;
-- Start a clean measurement (resets the statistics for the whole server)TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
-- A small "workload": the same lookups the app runs all daySELECT SUM(amount) FROM shop_orders WHERE customer_id = 1234;SELECT SUM(amount) FROM shop_orders WHERE customer_id = 2201;SELECT SUM(amount) FROM shop_orders WHERE customer_id = 4999;SELECT COUNT(*) FROM shop_orders WHERE order_id = 777;
SELECT LEFT(DIGEST_TEXT, 60) AS query_shape, COUNT_STAR AS runs, SUM_ROWS_EXAMINED AS rows_examined, SUM_NO_INDEX_USED AS runs_without_indexFROM performance_schema.events_statements_summary_by_digestWHERE SCHEMA_NAME = DATABASE() AND DIGEST_TEXT LIKE 'SELECT%shop_orders%'ORDER BY SUM_ROWS_EXAMINED DESC;+-------------+ | SUM(amount) | +-------------+ | 5460.00 | +-------------+ +-------------+ | SUM(amount) | +-------------+ | 4990.00 | +-------------+ +-------------+ | SUM(amount) | +-------------+ | 5610.00 | +-------------+ +----------+ | COUNT(*) | +----------+ | 1 | +----------+ +--------------------------------------------------------------+------+---------------+--------------------+ | query_shape | runs | rows_examined | runs_without_index | +--------------------------------------------------------------+------+---------------+--------------------+ | SELECT SUM ( `amount` ) FROM `shop_orders` WHERE `customer_i | 3 | 150000 | 3 | | SELECT COUNT ( * ) FROM `shop_orders` WHERE `order_id` = ? | 1 | 1 | 0 | +--------------------------------------------------------------+------+---------------+--------------------+
What this code does
- The three customer lookups share one digest: values became
?, so MySQL counts them as one query shape run 3 times, with 1,50,000 rows examined and no index in all 3 runs. That is the query to index, oncustomer_id. - The lookup by
order_iduses the primary key: 1 row examined. Only clear the summary table on your own practice server; on a shared server it wipes everyone's numbers.
Start with the queries that run most often or examine the most rows, and design one index for each group of similar queries. MySQL records this for you. performance_schema.events_statements_summary_by_digest groups statements by their shape (the digest, the text with values replaced by ?) and keeps counts, total time, rows examined and how many runs used no index. The slow query log writes every statement slower than long_query_time seconds to a file. Both point straight at the queries worth an index.
- Index the columns in
WHEREandJOIN ... ONof frequent queries; equality columns first, range column last. - Prefer one composite index that serves several queries over several single-column ones.
- Foreign key columns usually need an index for joins and for
ON DELETEchecks; MySQL creates one if you do not. - Before adding an index, check that an existing one cannot be extended instead; after adding it, prove the gain with rows examined or
EXPLAIN ANALYZE.
Next step
Check your understanding
11 quick questions