We use cookies to enhance your experience on the site

CREATE INDEX and UNIQUE INDEX

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

1CREATE 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:

1CREATE 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.

Go to CodeWorlds→