The Pinakes completed - summary

Congratulations, @name! You have finished the Catalog Hall - the hardest part of the Library. Your Pinakes is ready, the shelves are in order, and the Library finds every scroll fast and error-free. This is a great art that no ordinary scribe has mastered. Here is what you have mastered on this journey:

What you have learned

• An index is a catalog that speeds up searching - instead of scanning the whole table scroll by scroll, the database looks into it and goes straight to the right place.
• CREATE INDEX creates a catalog; a UNIQUE INDEX additionally guards against values repeating.
• We index columns used in

  WHERE

  ,

  JOIN

  and

  ORDER BY

  . the price is slower writes and more space taken, so we index with care.
• EXPLAIN shows the query plan - through the

  type

  and

  key

  columns you check whether MySQL used an index or laboriously searched the whole table.
• PRIMARY KEY (one, no NULL), UNIQUE (many, allows NULL) and a plain INDEX (allows duplicates) differ in the strictness of their rules, though all of them speed up reading.
• FOREIGN KEY links tables and guards referential integrity; the

  ON DELETE CASCADE

  and

  ON DELETE SET NULL

  rules decide the fate of related rows.
• Normalization (1NF, 2NF, 3NF) eliminates repetition: atomic values, full dependency on the key, and no transitive dependencies.

Map of the catalog

| Concept | What for | |---------|----------| | INDEX | fast searching | | UNIQUE | uniqueness of values | | FOREIGN KEY | consistency between tables | | Normalization | no redundancy |

What's next?

In the next location we will deal with transactions - the art of running many commands so that either all succeed or none leaves a trace. Your catalog is ready, so it is time to make writes safe. Onward, deeper into the Library!