SQL and MySQL course Β· Module 9: Indexes and normalization
Composite indexes and dropping indexes
In this lesson4
You already know single-column catalogs. Now I will teach you to build multi-column catalogs and to tidy away the ones that no longer serve the Library.
A composite (multi-column) index
Sometimes we search the shelves by two fields at once - for example, we look for a specific author's books in a given category. Then we create a composite index - a single catalog listing the scrolls by several columns:
CREATE INDEX idx_autor_kat ON ksiazki(autor_id, kategoria_id);Such an index speeds up a query filtering by both columns at the same time.
Column order matters
In a composite index the column order is not arbitrary. The catalog is sorted first by the first column, then - within it - by the second. It works best when you filter by the first column (or by the first and second together).
The index (autor_id, kategoria_id) helps wonderfully when you know autor_id. If you filter only by kategoria_id - this particular catalog is of little help. It is like a catalog arranged by author first: you find the author quickly, but you cannot look up the category directly in it.
Dropping an index
A redundant catalog is a burden - it slows down writes and takes up space. When an index is no longer needed, we remove it with the DROP INDEX command:
DROP INDEX idx_tytul ON ksiazki;We read it: "Drop the idx_tytul catalog from the ksiazki shelf". The shelf itself and its scrolls stay untouched - only the register that ordered them disappears.
When to tidy catalogs
- An index never used in queries is pure cost - drop it.
- After you change the way you search, old indexes are often redundant.
- A good scribe reviews the catalogs now and then and removes the dead ones - just as they tidy the shelves.
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What is a composite (multi-column) index?
2. Why does the order of columns in a composite index matter?
These are 2 of 3 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Code editor
Create a composite index named idx_autor_kat on the autor_id and kategoria_id columns in the ksiazki table.
- Code editor
Drop the index named idx_rok from the ksiazki table. Use DROP INDEX.
- Horizontal ordering
Arrange on a single line the correct command that drops an index: