Indexing

Indexes are crucial for optimizing query performance, especially in large datasets. By indexing attributes, you can significantly speed up query execution times. This tutorial demonstrates how to define and use an index within Onyx Database.

Steps to Use Indexing (Onyx Cloud Database)

1

Declare the Entity with an Indexed Field

Use the provided JSON schema to declare your entity with an indexed attribute and deploy the database using this schema definition.
1{
2  "tables": [
3    {
4      "name": "Book",
5      "identifier": {
6        "name": "bookId",
7        "type": "Long",
8        "generator": "Sequence"
9      },
10      "partition": "",
11      "attributes": [
12        {
13          "name": "bookId",
14          "type": "Long"
15        },
16        {
17          "name": "title",
18          "type": "String"
19        },
20        {
21          "name": "genre",
22          "type": "String"
23        },
24        {
25          "name": "description",
26          "type": "String"
27        }
28      ],
29      "relationships": [],
30      "indexes": [
31        {
32          "name": "genre"
33        }
34      ]
35    }
36  ],
37  "revisionDescription": "Schema for Book entity with indexed genre"
38}
  • The indexes array specifies which attributes are indexed. In this case, the genre attribute is indexed.
  • Ensure that the attribute names in the indexes array match the names in the attributes array.
  • Indexes can be applied to any attribute type and do not require uniqueness.
2

Create and Persist Test Data

Create instances of the Book entity with various genres and use the Persistence Manager to save them.
1// Create test data
2    const harryPotter = {
3        title: "Harry Potter, Deathly Hallows",
4        description: "Story about a kid that has abnormal creepy powers that seeks revenge on a poor innocent guy named Voldemort.",
5        genre: "CHILDREN"
6    };
7
8    const theGiver = {
9        title: "The Giver",
10        description: "Something about a whole community of color blind people.",
11        genre: "CHILDREN"
12    };
13    
14    const twilight = {
15        title: "Twilight",
16    genre: "CHILDREN",
17    description: "Book that led to an infamous teen vampire movie."
18    };
19    
20    const longWayDown = {
21        title: "Long Way Down",
22    genre: "TRAVEL",
23    description: "A captivating journey on two wheels."
24    };
25
26// Save book data
27await db.save('Book', harryPotter);
28await db.save('Book', theGiver);
29await db.save('Book', twilight);
30await db.save('Book', longWayDown);
3

Define and Execute a Query Using the Indexed Field

Use the indexed genre attribute to create a query that efficiently retrieves books of a specific genre.
1// Create a query to find children's books
2// Note: This query is optimized since the Book#genre attribute is indexed.
3const childrenBooks = await db.from('Book')
4    .where(eq('genre', 'CHILDREN'))
5    .list();
6
7console.log(`There are ${childrenBooks.length} children's books`);
  • Indexed attributes significantly improve query performance, especially when filtering large datasets.
  • In this example, the query retrieves all books with the genre CHILDREN.
Next Tutorial: Query Builder Syntax

Important Notes

  • Indexes improve query performance by reducing the amount of data the database needs to scan.
  • Adding indexes can introduce overhead during write operations, as the index needs to be maintained.
  • It's important to index attributes that are frequently used in query predicates.

Troubleshooting

  • Query Performance Not Improving: Ensure that the attribute used in the query predicate is properly indexed.
  • Incorrect Query Results: Verify that the index is correctly defined and that the data is consistent.
  • Write Performance Degradation: Be aware that excessive indexing can slow down write operations due to the overhead of maintaining indexes.

Next Steps

Now that you know how to use indexing to optimize your queries, you can explore advanced querying techniques: