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

The Pinakes completed - summary

2 min read Β·
In this lesson3

Congratulations! 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

ConceptWhat for
INDEXfast searching
UNIQUEuniqueness of values
FOREIGN KEYconsistency between tables
Normalizationno 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!

Spotted a mistake in this lesson?

  1. VideoModule summary

Useful articles