The Pinakes Catalog - what an index is

Welcome @name to the heart of my work - the Catalog Hall! It was here that the Pinakes, the first catalog in history, was born. Without it, finding a single scroll among hundreds of thousands would mean going shelf by shelf, row by row. In the world of MySQL we call such tedious scanning a full table scan.

Why does the Library need a catalog?

Imagine you are looking for a book titled Iliad. Without a catalog you would have to unroll every scroll and check its title. With a million scrolls that is hours of work!

The Pinakes changes everything: in it I wrote the titles in alphabetical order and noted the shelf number next to each one. Now you just glance at the catalog to know at once where the scroll lies.

An index in MySQL

An index is exactly the same thing - an extra structure the database builds alongside the table to find rows faster. Instead of reading the whole table, MySQL looks into the index and goes straight to the right place.

1SELECT * FROM ksiazki WHERE tytul = 'Iliada';

Without an index on the

tytul

column the database checks every row of the

ksiazki

table. With an index - it finds the Iliad almost instantly, just as I do in my catalog.

Worth remembering

• An index does not change the data in the table - it is a separate "catalog".
• An index speeds up reading (searching).
• Every table already has one index: on the primary key (

  id

  ).

In this location I will teach you to build your own catalogs, read query plans and arrange the shelves so the Library runs flawlessly. Let us begin!