How to create an index in MySQL or MariaDB

A well-chosen index turns a costly full table scan into a targeted lookup, keeping query latency stable as tables grow and traffic increases. Indexing the right columns also reduces CPU and I/O pressure by cutting down the number of rows that must be read and evaluated.

In MySQL and MariaDB, the common index type for InnoDB tables is a B-tree, which keeps key values ordered for efficient point lookups and range scans. The optimizer can use these indexes to speed up WHERE filtering, JOIN lookups, and some ORDER BY patterns when the index prefix matches the query’s leading predicates.

Indexes are not free: each one consumes disk space, uses memory in caches, and increases write cost because inserts, updates, and deletes must maintain the index structure. Creating an index on a large table can be a heavy operation that affects concurrency depending on engine and version, so plan changes carefully and validate query plans before and after.

1. Collect the SQL statement that needs a faster execution plan.

   mysql> SELECT id, first_name, last_name
       -> FROM users
       -> WHERE last_name = 'Case';

2. Run EXPLAIN for the query to record the current access method.

   mysql> EXPLAIN SELECT id, first_name, last_name
       -> FROM users
       -> WHERE last_name = 'Case'\G
   *************************** 1. ROW ***************************
              id: 1
     select_type: SIMPLE
           TABLE: users
            TYPE: ALL
   possible_keys: NULL
             KEY: NULL
   ##### snipped #####
            ROWS: 123456
           Extra: USING WHERE
   1 ROW IN SET (0.00 sec)

   Related: How to use EXPLAIN to view query plans in MySQL or MariaDB

3. List existing indexes on the target table before adding a new one.

   mysql> SHOW INDEX FROM users;
   +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
   | TABLE | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type  | Comment | Index_comment |
   +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
   | users |          0 | PRIMARY  |            1 | id          | A         |      123456 |     NULL | NULL   |      | BTREE       |         |               |
   +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
   1 ROW IN SET (0.00 sec)

4. Choose index columns and order based on selectivity and the leftmost prefix rule.

   Place the most selective equality columns first in composite indexes, then range or sort columns; example: CREATE INDEX idx_users_last_name_created ON users (last_name, created_at);

5. Create the index using CREATE INDEX.

   mysql> CREATE INDEX idx_users_last_name ON users (last_name);
   Query OK, 0 ROWS affected (0.42 sec)
   Records: 0  Duplicates: 0  Warnings: 0

   On large tables, index creation can take significant time and can reduce concurrency depending on engine, version, and online DDL behavior.

6. Confirm the new index name and column order.

   mysql> SHOW INDEX FROM users WHERE Key_name = 'idx_users_last_name';
   +-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
   | TABLE | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type  | Comment | Index_comment |
   +-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
   | users |          1 | idx_users_last_name |            1 | last_name  | A         |       54321 |     NULL | NULL   | YES  | BTREE       |         |               |
   +-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
   1 ROW IN SET (0.00 sec)

7. Re-run EXPLAIN to confirm the optimizer selects the new index.

   mysql> EXPLAIN SELECT id, first_name, last_name
       -> FROM users
       -> WHERE last_name = 'Case'\G
   *************************** 1. ROW ***************************
              id: 1
     select_type: SIMPLE
           TABLE: users
            TYPE: REF
   possible_keys: idx_users_last_name
             KEY: idx_users_last_name
   ##### snipped #####
            ROWS: 42
           Extra: USING WHERE
   1 ROW IN SET (0.00 sec)

   If the plan does not change, refreshing statistics can help; example: ANALYZE TABLE users;

   Related: How to use EXPLAIN to view query plans in MySQL or MariaDB

8. Drop redundant or unused indexes when they add write overhead without measurable benefit.

   mysql> DROP INDEX idx_unused ON users;
   Query OK, 0 ROWS affected (0.03 sec)
   Records: 0  Duplicates: 0  Warnings: 0

   Dropping an index can change query plans immediately, so validate before removing indexes on production systems.