Composite indexes and dropping indexes

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:

1CREATE 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:

1DROP 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.