MySQL Indexes and Reading EXPLAIN Output

An index is a sorted structure that lets MySQL find rows without scanning the whole table. A query on an unindexed column forces a full table scan, which becomes slow as the table grows.

Create an index on the columns you filter or join on:

bash mysql -u app -p shop -e "CREATE INDEX idxorderscustomer ON orders (customerid, createdat);"

The column order matters. This composite index helps queries that filter on customerid alone and queries that filter on customerid and sort by createdat, but not queries that filter only on createdat.

To check whether MySQL uses an index, prefix the query with EXPLAIN:

bash mysql -u app -p shop -e "EXPLAIN SELECT FROM orders WHERE customerid = 42 ORDER BY createdat;"

Read these columns first:

- type: ALL means a full scan, while ref or range means an index is used. - key: the index chosen, or NULL when none is used. - rows: the estimated number of rows examined. - Extra: Using filesort or Using temporary are warning signs.

Use EXPLAIN ANALYZE on MySQL 8.0.18 and later to see actual timings. Avoid adding indexes blindly, because each index slows writes and uses disk space. Drop unused ones after checking the query workload.