# Keys and indexes · Mastering Rell · Learn Chromia

> An index lets the database find data faster. This lesson covers how indexes work in Rell and when to use them.

Source: https://chromia.com/learn/rell-masterclass/indexes/

Mastering RellLesson 4 of 8

1. [01Mastering Rell](https://chromia.com/learn/rell-masterclass/introduction/)
2. [02Comparing SQL tables and Rell entities](https://chromia.com/learn/rell-masterclass/entities/)
3. [03Code optimization example](https://chromia.com/learn/rell-masterclass/example/)
4. [04Keys and indexes](https://chromia.com/learn/rell-masterclass/indexes/)
5. [05INNER JOIN statement](https://chromia.com/learn/rell-masterclass/join/)
6. [06SELECT statement](https://chromia.com/learn/rell-masterclass/select/)
7. [07Subqueries](https://chromia.com/learn/rell-masterclass/sub-queries/)
8. [08UPDATE statement](https://chromia.com/learn/rell-masterclass/update/)

[Learn](https://chromia.com/learn/)[Mastering Rell](https://chromia.com/learn/rell-masterclass/)

04 / 08

# Keys and indexes

An index lets the database find data faster. This lesson covers how indexes work in Rell and when to use them.

Suppose you need to locate one entry in a large dataset. Without an index, the database may have to scan every row, which takes *O(n)* time in the worst case. When you index an attribute in Rell, lookups drop to *O(log(n))*, because PostgreSQL stores the index in a tree structure.

Rell gives you two ways to index an attribute: `key` and `index`. Both speed up queries, but each has a distinct role:

## Key

A `key` is an index that also requires the attribute's value to be unique. Two entities can't share the same value for that attribute, which protects you from accidentally creating duplicate entries.

## Index

Use an `index` to speed up queries on attributes whose values don't have to be unique.

note

Keys and indexes both make *creating* entities slower, so only index the attributes that need it.

## Key and index example

Take a street with several houses on it, where each house has its own unique id. You can model this as follows:

rell

```
entity house {
    key id: integer;
    index street: text;
}
```

With this model, the following queries run efficiently:

rell

```nolines
val unique_house = house @ { .id == 10 };
val houses_on_main_street = house @* { .street == "main street" };
```

## Composite index

A composite index covers several attributes at once and helps when you look up entries by a combination of values. If your app frequently fetches every house a given owner has on a given street, define a multi-column index:

rell

```nolines
index owner, street;
```

Column order in a multi-column index affects performance. As in SQL, the columns are ordered, and each one comes before the next in the index. Put the most significant attribute first to get the most efficient index. For example, to find all houses that have exactly four rooms and more than 100 units of floor area:

rell

```nolines
house @ { .number_of_rooms == 4, .floor_area > 100 };
```

The best index for this query is:

rell

```nolines
index number_of_rooms, floor_area;
```

Composite keys follow the same rules. When a particular combination of values must be unique, a composite key enforces that in the database. For the best performance, list first the attribute that matches the fewest results.

## Practical example

Here you create `house` records and then query for specific ones.

Open `src/main/entities.rell` and add the `house` entity:

src/main/entities.rell

```
entity house {
  index street;
  number: integer;
  key street, number;
  number_of_rooms: integer;
  number_of_floors: integer;
  floor_area: integer;
  index number_of_rooms, floor_area;
}
```

Next, add the following code to `src/main/queries.rell` so you can query `house` records:

src/main/queries.rell

```nolines
query get_specific_houses(number_of_rooms: integer, floor_area: integer) = house @* { .number_of_rooms == number_of_rooms, .floor_area >= floor_area } ( $.to_struct() );
```

Start a local node so you can work with the database. Pass the `--wipe` flag when you want to clear all existing records:

shell

```nolines
chr node start --wipe
```

Create a street record:

shell

```nolines
chr tx create_street "Drottninggatan"
```

Call the `create_house` operation to add three houses:

shell

```
chr tx create_house 1 30 4 1 200
chr tx create_house 1 31 4 1 100
chr tx create_house 1 32 4 1 109
```

Now query the database for specific houses:

shell

```nolines
chr query get_specific_houses
```

The query returns:

result

```
[
  [
    "floor_area": 200,
    "number": 30,
    "number_of_floors": 1,
    "number_of_rooms": 4,
    "street": 1
  ],
  [
    "floor_area": 109,
    "number": 32,
    "number_of_floors": 1,
    "number_of_rooms": 4,
    "street": 1
  ]
]
```

Written for this site from Chromia’s public course. Code samples are the course’s own.

[← Previous**Code optimization example**](https://chromia.com/learn/rell-masterclass/example/)[Next →**INNER JOIN statement**](https://chromia.com/learn/rell-masterclass/join/)

On this page

* [Key](#key)
* [Index](#index)
* [Key and index example](#key-and-index-example)
* [Composite index](#composite-index)