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

CREATE INDEX and UNIQUE INDEX

2 min read Β·
In this lesson4

Now that you know what an index is, it is time to create your own catalog. The CREATE INDEX command does this.

Creating an index

CREATE INDEX idx_tytul ON ksiazki(tytul);

We read it as: "Create a catalog named idx_tytul that lists the ksiazki shelf by the tytul column". From now on searching by title will be lightning-fast. By convention we begin index names with idx_ so they are easy to recognize.

A unique index

Sometimes we want the values in a column not to repeat - for example, every reader must have a different email address. Then we create a UNIQUE INDEX:

CREATE UNIQUE INDEX idx_email ON czytelnicy(email);

Such an index works like an ordinary catalog, but it additionally guards against any address appearing twice. An attempt to add a second reader with the same email will fail with an error.

When to create an index?

Index columns you often use for:

  • filtering - in WHERE (e.g. WHERE tytul = ...),
  • joining tables - in JOIN (e.g. autor_id),
  • sorting - in ORDER BY.

The cost of indexes

A catalog must be updated with every change of the scrolls. The same goes for an index:

  • it slows down writes (INSERT, UPDATE, DELETE),
  • it takes up extra space on disk.

That is why we do not index everything blindly - we create catalogs only where they will truly speed up the Library's work.

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. Which command correctly creates an index on the tytul column in the ksiazki table?

  2. 2. What is a UNIQUE INDEX used for?

These are 2 of 4 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Click in order

    Click the elements in the order in which we build the command that creates an index:

  • Code editor

    Create an index named idx_tytul on the tytul column in the ksiazki table, to speed up searching for books by title.

  • Code editor

    Create a unique index named idx_email on the email column in the czytelnicy table, so that no address can repeat.

  • Horizontal ordering

    Arrange on a single line the correct command that creates a unique index on the email address:

  • Code editor

    Create an index named idx_rok on the rok_wydania column in the ksiazki table.

  • Horizontal ordering

    Arrange on a single line the correct command that creates an index on the year of publication:

  1. QuestionCREATE INDEX syntax
  2. QuestionWhat a UNIQUE INDEX is for
  3. Click in orderCreating the idx_tytul index
  4. Coding taskIndex on the title
  5. QuestionWhen to add an index
  6. Coding taskUnique index on email
  7. QuestionThe cost of indexes
  8. Put in orderCreating the idx_email index
  9. Coding taskIndex on the year of publication
  10. Put in orderCreating the idx_rok index

Useful articles