SQL and MySQL course Β· Module 9: Indexes and normalization

Composite indexes and dropping indexes

2 min read Β·
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. 1. What is a composite (multi-column) index?

  2. 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:

  1. QuestionComposite index
  2. QuestionColumn order in an index
  3. Coding taskComposite index
  4. QuestionDropping an index
  5. Coding taskDrop a redundant index
  6. Put in orderDropping the idx_tytul index

Useful articles