SQL and MySQL course Β· Module 9: Indexes and normalization
The Pinakes completed - summary
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,JOINandORDER BY. the price is slower writes and more space taken, so we index with care. - EXPLAIN shows the query plan - through the
typeandkeycolumns 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 CASCADEandON DELETE SET NULLrules 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!
Spotted a mistake in this lesson?