Level 11 of 13 · The index at the back of the book

SQL Indexes Explained for Beginners: CREATE INDEX and EXPLAIN

What an index is, why it makes lookups fast, unique and multi-column indexes, and how EXPLAIN shows whether a query uses one.

Covers: CREATE INDEX · UNIQUE INDEX · multi-column indexes · EXPLAIN · DROP INDEX

Practise Indexes

The lesson

Every example runs against the same small pizza shop database: pizzas, customers, orders and staff.

# Finding a row without an index An index helps the database find rows fast, like the index at the back of a book. This slide shows why you need one: without it, the database has to read every row. To find one email with no index, the database reads every row. On 7 rows that's instant; on 7 million it isn't. - `EXPLAIN QUERY PLAN` shows how the database will run a query (in MySQL: EXPLAIN) - `SCAN` it reads every row > In MySQL's EXPLAIN, type ALL means a full scan. On a big table, it's the first thing to look for in a slow query.

EXPLAIN QUERY PLAN SELECT *
FROM customers
WHERE email = 'dev@mail.com'

# CREATE INDEX An index is a sorted list of one column's values, each pointing at its row, like the index at the back of a book. - `CREATE INDEX idx_customers_email ON customers(email)` index the email column - `SEARCH … USING INDEX` the plan now jumps straight to the row > Name indexes idx_table_column, so the name tells you what they cover.

CREATE INDEX idx_customers_email
  ON customers(email);
EXPLAIN QUERY PLAN SELECT *
FROM customers
WHERE email = 'dev@mail.com'

# UNIQUE index: no duplicates - `CREATE UNIQUE INDEX` an index that also refuses duplicate values Two customers can't share an email now, so this INSERT is refused. > A UNIQUE index still allows several NULLs: NULL isn't equal to anything, not even another NULL.

CREATE UNIQUE INDEX
  ux_customers_email
  ON customers(email);
INSERT INTO customers
VALUES (8, 'Copy Cat', 'Leeds',
  'dev@mail.com', NULL)

# An index on two columns - `(status, customer_id)` sorted by status, then by customer within each status It helps queries on status, or on status and customer_id together. It doesn't help a query on customer_id alone, just as a phone book sorted by surname doesn't help you find a first name. > Put the column you always filter on first.

CREATE INDEX
  idx_orders_status_customer
  ON orders(status, customer_id);
EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE status = 'delivered'
  AND customer_id = 1

# Indexes have a cost Every INSERT, UPDATE and DELETE has to update the indexes too. Index the columns you search, join and sort on, not every column. - `DROP INDEX` removes an index > Primary keys are indexed automatically. Foreign key columns are the next thing to index, because joins run on them.

CREATE INDEX idx_pizzas_price
  ON pizzas(price);
DROP INDEX idx_pizzas_price
  ON pizzas;
EXPLAIN QUERY PLAN SELECT *
FROM pizzas
WHERE price > 10

# Reading MySQL's EXPLAIN At work you'll run EXPLAIN on MySQL, which shows a table instead of SCAN and SEARCH. Here is the email lookup, before and after the index. - `type` how the rows are found: ALL means every row (SCAN); ref, eq_ref and const mean an index is used (SEARCH) - `key` the index used, NULL when there is none - `rows` roughly how many rows MySQL expects to read > Read type and key first. type ALL with key NULL on a big table is the classic missing index.

EXPLAIN SELECT * FROM customers
WHERE email = 'dev@mail.com';

-- before the index:
--   type: ALL
--   key:  NULL
--   rows: 7

-- after CREATE INDEX
-- idx_customers_email:
--   type: ref
--   key:  idx_customers_email
--   rows: 1

Appian corner

Index the columns your record types filter, sort and relate on (customer_id on orders, for example) and the columns in your query filters. When a record list, a query or a sync is slow, a missing index is one of the first things to check.

Practice questions

Try each one before opening the answer. To write and run them against the database in your browser, practise the level.

1. Index orders on customer_id.

CREATE ___ idx_orders_customer ___
  orders(customer_id)

Show the answer

CREATE INDEX idx_orders_customer ON
  orders(customer_id)

CREATE INDEX name ON table(column)

2. EXPLAIN QUERY PLAN says 'SCAN orders'. What does that mean?
- It uses an index
- It reads every row of orders
- orders is empty
- The query failed
Show the answer
It reads every row of orders. SCAN reads the whole table. SEARCH … USING INDEX is the fast path. MySQL’s EXPLAIN says the same thing as type ALL (every row) or ref (using an index).
3. Most queries join orders to customers. Which orders column most deserves an index?
- tip
- qty
- customer_id
- id (the primary key)
Show the answer
customer_id. Joins and filters run on customer_id. The primary key already has its own index.
4. A record list in Appian is slow. On the live MySQL database, EXPLAIN gives this. What should you do?
```
EXPLAIN SELECT * FROM orders
WHERE customer_id = 42;

--   type: ALL
--   key:  NULL
--   rows: 480000
```
- Nothing, ALL means all is well
- Add an index on orders(customer_id)
- Add LIMIT 1 to the query
- Drop the primary key
Show the answer
Add an index on orders(customer_id). type ALL with key NULL means MySQL reads all 480,000 rows. An index on customer_id turns it into ref, reading only that customer’s rows.
5. Make sure no two customers can ever share an email, with an index.
Show the answer
```
CREATE UNIQUE INDEX
  ux_customers_email
  ON customers(email)
```
CREATE UNIQUE INDEX any_name ON customers(email)
6. Nobody searches orders by tip after all. Remove the index.
Show the answer
```
CREATE INDEX idx_orders_tip
  ON orders(tip);
DROP INDEX idx_orders_tip ON orders
```
DROP INDEX idx_orders_tip ON orders
7. Boss question! Create one index on orders that helps queries filtering on status, and on status and customer_id together.
Show the answer
```
CREATE INDEX
  idx_orders_status_customer
  ON orders(status, customer_id)
```
One index, two columns, status first: ON orders(status, customer_id)

Practise Indexes