Skip to content
DBAExperts
MySQL Performance

How to create an index in MySQL and when to use a composite one

An index speeds up reads at the cost of slightly slowing writes and taking up space. The goal is not to have many indexes, but the right ones: those that serve your most frequent or most expensive queries.

  1. 1

    Identify the column that filters or joins

    Index columns that appear in WHERE, JOIN, or ORDER BY. An index on a column nobody filters by is dead weight.

    EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 42;
  2. 2

    Create a simple index

    On a large table, creating an index can take time and consume resources. In production, do it during a low-traffic window.

    CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);
  3. 3

    Use a composite index when you filter by several columns

    Order matters: put the most selective column first, or the one you use for equality, and the range or sort column after it. This index serves queries filtering by cliente_id, or by cliente_id and fecha together.

    CREATE INDEX idx_pedidos_cliente_fecha ON pedidos (cliente_id, fecha);
  4. 4

    Confirm that the query uses the index

    Check the plan with EXPLAIN: look for the key column to no longer be NULL and for rows to drop compared to a full scan.

    EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 42 ORDER BY fecha;

// common mistake

A composite index (a, b) works for filtering by a, or by a and b, but NOT for filtering by b alone. It is the most common mistake: creating the index in the wrong order and believing it covers a query that actually does a full scan.

// when it's worth an expert

When adding indexes no longer helps, or even makes writes worse, the problem usually lies in the query design or the schema. At dba.mx we tune indexes based on the real execution plan, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.