--- title: "Indexing & data model considerations | SurrealDB University" description: "Indexing & data model considerations. A chapter of SurrealDB Fundamentals, a hands-on course with runnable examples." url: https://surrealdb.com/learn/fundamentals/performance/index-data-model --- [Back to Courses](https://surrealdb.com/learn) Course chapters [SurrealDB Fundamentals](https://surrealdb.com/learn/fundamentals) [Introduction](https://surrealdb.com/learn/fundamentals) [Welcome to SurrealDB University](https://surrealdb.com/learn/fundamentals/intro/welcome) [Intro to SurrealDB](https://surrealdb.com/learn/fundamentals/intro/surrealdb) [Why SurrealQL is SQL-like](https://surrealdb.com/learn/fundamentals/intro/surrealql) [Part 1: Schemaless CRUD](https://surrealdb.com/learn/fundamentals/schemaless) [Introduction](https://surrealdb.com/learn/fundamentals/schemaless) [Record IDs](https://surrealdb.com/learn/fundamentals/schemaless/record-ids) [Inserting data](https://surrealdb.com/learn/fundamentals/schemaless/inserting-data) [Reading data](https://surrealdb.com/learn/fundamentals/schemaless/reading-data) [Updating data](https://surrealdb.com/learn/fundamentals/schemaless/updating-data) [Deleting data](https://surrealdb.com/learn/fundamentals/schemaless/deleting-data) [Part 2: Adding relationships](https://surrealdb.com/learn/fundamentals/relationships) [Introduction](https://surrealdb.com/learn/fundamentals/relationships) [Graph relations](https://surrealdb.com/learn/fundamentals/relationships/graph-relations) [Record links](https://surrealdb.com/learn/fundamentals/relationships/record-links) [Relational style joins](https://surrealdb.com/learn/fundamentals/relationships/relational-style) [Part 3: Making it schemafull](https://surrealdb.com/learn/fundamentals/schemafull) [Introduction](https://surrealdb.com/learn/fundamentals/schemafull) [Define tables, views and changefeeds](https://surrealdb.com/learn/fundamentals/schemafull/define-table) [Define fields, constraints and assertions](https://surrealdb.com/learn/fundamentals/schemafull/define-fields) [Schemafull CRUD](https://surrealdb.com/learn/fundamentals/schemafull/schemafull-crud) [Part 4: Making it secure](https://surrealdb.com/learn/fundamentals/security) [Introduction](https://surrealdb.com/learn/fundamentals/security) [Authentication](https://surrealdb.com/learn/fundamentals/security/authentication) [Query capabilities](https://surrealdb.com/learn/fundamentals/security/query-capabilities) [Part 5: Making it performant](https://surrealdb.com/learn/fundamentals/performance) [Introduction](https://surrealdb.com/learn/fundamentals/performance) [Indexing & data model considerations](https://surrealdb.com/learn/fundamentals/performance/index-data-model) [Deployment & storage layer considerations](https://surrealdb.com/learn/fundamentals/performance/deployment-storage) [Completion](https://surrealdb.com/learn/fundamentals/completion) Certification Pending completion # Indexing & data model considerations Fundamental to performance in any database is how it handles indexing. In this lesson, we're focusing on: - User-defined indexes: indexes created explicitly by the user that are typically used as secondary indexes alongside the primary index. - General data modeling and query optimisation tips. ## Creating indexes SurrealDB offers a range of indexing capabilities designed to optimise data retrieval and search efficiency. We have: - Traditional indexes such as single field indexes, composite indexes, unique indexes, and count indexes. - Specialized indexes such as full-text search indexes and vector search indexes. We'll go over traditional indexing examples and a full-text search example, but leave vector search for another time. If you're interested in vector search right now, you can check out our [vector search reference guide](https://surrealdb.com/docs/learn/data-models/vector-search/overview), which explains how it works with full-text search and vector functions. ### Traditional indexes A user-defined index is called a secondary index because it's a secondary data structure that maintains a copy of part of your data. It copies the data from the primary index, which is your primary data structure that contains all of your data. You can change the primary index by changing the storage layer as we'll explore in the next lesson. That's enough computer science for now, for the rest of this lesson we are just going to refer to secondary indexes as indexes. The important thing to remember is simply that when you add an index, you are creating a copy of part of your data, which the database has to keep up to date anytime you do a write operation (Create, Update, Delete) to the tables you created the index on. This means adding an index is always a compromise between impacting your write performance to improve your read performance. Which is why we would never just add an index to everything. So, where should we add indexes? There is no one-size-fits-all answer to this as it depends on how you are querying the database. We might not even need an index for certain queries as it wouldn't improve the read performance. That is the case for simple CRUD operations that rely on using record IDs, such as this `SELECT` query that is selecting two fields from a single record or a range of records. ``` SELECT name, emailFROM person:01FTP9H7BG8VDANQPN8J3Y857R;SELECT name, emailFROM person:01FTP9H7BG8VDANQPN8J3Y857R..=01HG9EFC0R8DA8F87VNYP0CD8A; ``` ![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query The reason why it doesn't need an index is because it's already using an index: the primary index of the storage engine you chose for your storage layer. When writing our queries like this next one that we normally use in relational databases, we may want an index because otherwise it results in a table scan instead of directly fetching records. ``` SELECT name, email FROM personWHERE id = person:01FTP9H7BG8VDANQPN8J3Y857R;SELECT name, email FROM personWHERE time.created_at >= d'2022-01-30T20:06:30Z'AND time.created_at <= d'2023-11-27T22:30:23Z'; ``` ![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query To improve this we can define an index on the field we are using in our query with the `DEFINE INDEX` statement. To make sure that we know that the query is using the index, we can check by adding the `EXPLAIN` clause on the `SELECT` statement. ``` DEFINE INDEX person_time ON TABLE person FIELDS time.created_at;SELECT name, email FROM personWHERE time.created_at >= d'2022-01-30T20:06:30Z'AND time.created_at <= d'2023-11-27T22:30:23Z'EXPLAIN; ``` ![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query If we see that we are using `Iterate Index` instead of `Iterate Table` then we know we are using the index. Looking at the performance of the query now with an added index, we can see that there is a huge improvement in performance. However, it is still slower than our original query using record IDs - not only because a secondary index lookup is less direct than a primary-key fetch, but also because this query returns hundreds of records rather than one. ``` SELECT name, email FROM personWHERE time.created_at >= d'2022-01-30T20:06:30Z'AND time.created_at <= d'2023-11-27T22:30:23Z'; ``` ![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query This is why record IDs are so important, as you've heard me say throughout the course. The process we just went through is a good way to start optimising your queries, which is as follows: - Start by using record IDs whenever possible - If not possible, such as needing a `WHERE`, `GROUP BY` or `ORDER BY` fields other then our Record ID - Then we look at the common fields that we are using and `DEFINE INDEX` for those fields - We then use the `EXPLAIN` clause to check if the index is working, or if we need to adjust it. - Also importantly check if the index is actually improving the performance as there might not be a noticeable difference for small tables. ### Unique indexes Moving on to our next example, here we have a composite index, meaning an index that has more than one field. It's also a `UNIQUE` index, meaning it has a `UNIQUE` constraint. This is not only for improving read performance, but the `UNIQUE` constraint also ensures that the `order` table always has a `UNIQUE` combination of the `in` and `out` fields. ``` DEFINE INDEX unique_wishlist_relationships ON TABLE wishlist FIELDS in, out UNIQUE;-- This query worksRELATE person:01GPNJBVN09WSRFC3ZMEE6NDRG->wishlist->product:01HJN9QPNG9JAAAV19FT3GKZP0 SET colour = "Blue", size = "M", time.created_at = time::now();-- This duplicate won't, thanks to the indexRELATE person:01GPNJBVN09WSRFC3ZMEE6NDRG->wishlist->product:01HJN9QPNG9JAAAV19FT3GKZP0 SET colour = "Blue", size = "M", time.created_at = time::now(); ``` ![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query Since the `in` and `out` fields signify a relationship, this means we cannot insert a duplicate relationship into the order table. Another way of looking at this, is that the `UNIQUE` index we are creating here functions in a similar way as a multi-field schema constraint. Because as we saw in part 3 where we made things schemafull, you can only `DEFINE` a single `TABLE` or a single `FIELD` at a time. ### Count indexes A count index is a really easy one to put together, because it applies to a whole table and has a single `COUNT` clause. With this index, using `count()` in a statement like `SELECT` with `GROUP ALL` will access the indexed value for the number of records, instead of counting them every time. This is definitely an index to use if you have a large number of records that you need to know the exact number of. ``` DEFINE INDEX IF NOT EXISTS review_count ON TABLE review COUNT; ``` ![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query ### Full-text search indexes In addition to traditional indexes, SurrealDB also supports full-text search indexes. We won't cover these in detail here as they're highly configurable with many advanced features like basic and advanced text matching, proximity searches, result ranking, and keyword highlighting. If you want to dive deeper, check out our [reference guide for full-text search](https://surrealdb.com/docs/learn/data-models/full-text-search/overview). ``` DEFINE ANALYZER IF NOT EXISTS blank_snowball TOKENIZERS blank FILTERS lowercase, snowball(english);DEFINE INDEX IF NOT EXISTS review_content ON TABLE review FIELDS review_text FULLTEXT ANALYZER blank_snowball BM25; ``` ![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query The definition is made up of 2 parts. First, we define the `ANALYZER` which uses `TOKENIZERS` to split our text into words. Here we are using `blank` to split based on spaces. Then we also need to `DEFINE INDEX` like we normally would on either single or multiple text `FIELDS` and then add the `FULLTEXT ANALYZER` we defined previously, selecting the `BM25` ranking algorithm, BM simply meaning Best Match. Once we've done this we can double at `@@` characters in our `SELECT` statement to perform the search. ``` SELECT id, rating, review_text FROM reviewWHERE review_text @@ 'wears nonstop'; ``` ![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query We can see it's not just doing an exact search from the results but rather a semantic search. One of the results holds this bit of text that includes not just "nonstop" but also "wearing", even though we did a search using a string using the form "wears". That's thanks to the snowball filter that reduces words to more basic forms, like "wears" and "wearing" to just "wear". ```surql "I've been wearing this hoodie nonstop since I got it. The quality is amazing and the color is gorgeous. I've gotten so many compliments!" ``` ### One big table (OBT) vs third normal form (3NF) Moving onto data modelling, it's important to know that the perfect data model doesn't exist. The best way to model your data sits on a spectrum between two extremes. On one end we have the "One Big Table" (OBT), which as the name suggests, advocates for putting as much as possible in a single table. The other end of the spectrum is called the third normal form (3NF), which is the traditional data modelling approach for relational databases since the 1970s. 3NF advocates for putting as little as possible in a single table. I can't tell you exactly how to model your data as the most performant data model is generally the one that most closely matches the business logic of your application. That's why we think it's important for you to be able to prototype and iterate quickly, and then lock down your schema once you have found what works. That's the reason this course follows this particular journey from schemaless prototyping to schemafull constraints, and why SurrealDB holds to this approach in the first place. ## Summary Database performance is all about finding the right balance. Adding an index is a balance between impacting your write performance and improving your read performance. Finding the most performant data model is a balance between putting everything in as few tables as possible and as many tables as possible. I hope you have enjoyed the journey so far and I'll see you in our last lesson. Previous Part 5: Making it performant [Previous](https://surrealdb.com/learn/fundamentals/performance) Next lesson Deployment & storage layer considerations [Next lesson](https://surrealdb.com/learn/fundamentals/performance/deployment-storage) ```json {"@context":"https://schema.org","@type":"Course","name":"SurrealDB Fundamentals","description":"The most efficient way to learn SurrealDB through guided hands-on learning","url":"https://surrealdb.com/learn/fundamentals","inLanguage":"en","isAccessibleForFree":false,"provider":{"@type":"Organization","name":"SurrealDB","url":"https://surrealdb.com"},"hasPart":[{"@type":"LearningResource","name":"SurrealDB Fundamentals","url":"https://surrealdb.com/learn/fundamentals"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals"},{"@type":"LearningResource","name":"Welcome to SurrealDB University","url":"https://surrealdb.com/learn/fundamentals/intro/welcome"},{"@type":"LearningResource","name":"Intro to SurrealDB","url":"https://surrealdb.com/learn/fundamentals/intro/surrealdb"},{"@type":"LearningResource","name":"Why SurrealQL is SQL-like","url":"https://surrealdb.com/learn/fundamentals/intro/surrealql"},{"@type":"LearningResource","name":"Part 1: Schemaless CRUD","url":"https://surrealdb.com/learn/fundamentals/schemaless"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/schemaless"},{"@type":"LearningResource","name":"Record IDs","url":"https://surrealdb.com/learn/fundamentals/schemaless/record-ids"},{"@type":"LearningResource","name":"Inserting data","url":"https://surrealdb.com/learn/fundamentals/schemaless/inserting-data"},{"@type":"LearningResource","name":"Reading data","url":"https://surrealdb.com/learn/fundamentals/schemaless/reading-data"},{"@type":"LearningResource","name":"Updating data","url":"https://surrealdb.com/learn/fundamentals/schemaless/updating-data"},{"@type":"LearningResource","name":"Deleting data","url":"https://surrealdb.com/learn/fundamentals/schemaless/deleting-data"},{"@type":"LearningResource","name":"Part 2: Adding relationships","url":"https://surrealdb.com/learn/fundamentals/relationships"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/relationships"},{"@type":"LearningResource","name":"Graph relations","url":"https://surrealdb.com/learn/fundamentals/relationships/graph-relations"},{"@type":"LearningResource","name":"Record links","url":"https://surrealdb.com/learn/fundamentals/relationships/record-links"},{"@type":"LearningResource","name":"Relational style joins","url":"https://surrealdb.com/learn/fundamentals/relationships/relational-style"},{"@type":"LearningResource","name":"Part 3: Making it schemafull","url":"https://surrealdb.com/learn/fundamentals/schemafull"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/schemafull"},{"@type":"LearningResource","name":"Define tables, views and changefeeds","url":"https://surrealdb.com/learn/fundamentals/schemafull/define-table"},{"@type":"LearningResource","name":"Define fields, constraints and assertions","url":"https://surrealdb.com/learn/fundamentals/schemafull/define-fields"},{"@type":"LearningResource","name":"Schemafull CRUD","url":"https://surrealdb.com/learn/fundamentals/schemafull/schemafull-crud"},{"@type":"LearningResource","name":"Part 4: Making it secure","url":"https://surrealdb.com/learn/fundamentals/security"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/security"},{"@type":"LearningResource","name":"Authentication","url":"https://surrealdb.com/learn/fundamentals/security/authentication"},{"@type":"LearningResource","name":"Query capabilities","url":"https://surrealdb.com/learn/fundamentals/security/query-capabilities"},{"@type":"LearningResource","name":"Part 5: Making it performant","url":"https://surrealdb.com/learn/fundamentals/performance"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/performance"},{"@type":"LearningResource","name":"Indexing \u0026 data model considerations","url":"https://surrealdb.com/learn/fundamentals/performance/index-data-model"},{"@type":"LearningResource","name":"Deployment \u0026 storage layer considerations","url":"https://surrealdb.com/learn/fundamentals/performance/deployment-storage"},{"@type":"LearningResource","name":"Completion","url":"https://surrealdb.com/learn/fundamentals/completion"}]} ``` ```json {"@context":"https://schema.org","@type":"LearningResource","name":"Indexing \u0026 data model considerations","description":"Indexing \u0026 data model considerations","url":"https://surrealdb.com/learn/fundamentals/performance/index-data-model","learningResourceType":"lesson","isPartOf":{"@type":"Course","name":"SurrealDB Fundamentals","url":"https://surrealdb.com/learn/fundamentals"},"position":29} ``` ```json {"@context":"https://schema.org","@type":"Organization","name":"SurrealDB","url":"https://surrealdb.com","logo":"https://surrealdb.com/assets/static/logo.BG7_TG2b.svg","description":"SurrealDB is the unified data layer for AI. A multi-model database for documents, graphs, vectors, and time-series.","foundingDate":"2022","hasCertification":[{"@type":"Certification","name":"SOC 2 Type 2"},{"@type":"Certification","name":"GDPR"},{"@type":"Certification","name":"Cyber Essentials Plus"},{"@type":"Certification","name":"ISO 27001"}],"owns":[{"@type":"SoftwareApplication","name":"SurrealDB","url":"https://surrealdb.com/surrealdb"},{"@type":"SoftwareApplication","name":"Agent Memory","url":"https://surrealdb.com/agent-memory"}],"knowsAbout":["multi-model databases","document databases","graph databases","vector search","time-series databases","SurrealQL","Agent Memory","real-time databases","embedded databases","context layer","graph ontology","distributed database","knowledge graphs","distributed transaction protocols","highly-scalable databases"],"sameAs":["https://www.wikidata.org/wiki/Q124316308","https://github.com/surrealdb/surrealdb","https://twitter.com/surrealdb","https://www.youtube.com/@surrealdb","https://www.linkedin.com/company/surrealdb","https://discord.gg/surrealdb","https://www.reddit.com/r/surrealdb","https://www.instagram.com/surrealdb","https://medium.com/surrealdb","https://dev.to/surrealdb"]} ``` ```json {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://surrealdb.com"},{"@type":"ListItem","position":2,"name":"Learn","item":"https://surrealdb.com/learn"},{"@type":"ListItem","position":3,"name":"Index data model","item":"https://surrealdb.com/learn/fundamentals/performance/index-data-model"}]} ```