Speed Up Queries With CREATE INDEX

Create an index on one or more columns to make lookups and joins faster.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Create an index on customer_id and verify the query still returns correct results

The index is transparent to query results — the SELECT returns the same rows as it would without the index. The benefit is speed: on large tables, the database uses the index to find customer_id = 1 rows without scanning every order. Use CREATE UNIQUE INDEX to also enforce uniqueness on the indexed column.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE orders (id INT, customer_id INT, status VARCHAR(20));

INSERT INTO
  orders (id, customer_id, status)
VALUES
  (1, 1, 'pending'),
  (2, 2, 'shipped'),
  (3, 1, 'shipped');

Validated query Shared across supported engines.

SQL

CREATE INDEX idx_orders_customer ON orders (customer_id);

SELECT
  id,
  customer_id,
  status
FROM
  orders
WHERE
  customer_id = 1
ORDER BY
  id;

Expected result Returned rows for the shared example.

id	customer_id	status
1	1	pending
3	1	shipped

Output is identical across all engines.

Useful when

Where this command helps.

speeding up queries that filter or join on a frequently used column
reducing full table scans on large tables

Explanation

What the command is doing.

An index is a data structure the database maintains alongside a table to allow faster row lookups by a specific column. Without an index, the database scans every row. With one, it can jump directly to matching rows. Indexes speed up WHERE filters, JOIN conditions, and ORDER BY sorts on indexed columns. The trade-off is slightly slower writes and additional storage.

Speed Up Queries With CREATE INDEX

Create an index on customer_id and verify the query still returns correct results

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.