Indexing is a way of sorting a number of records on multiple fields.
Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. They help when searching through large, unordered data sets and give the database search engine a quick way to sort and find data. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access to ordered records.
Think about a physical textbook. That book has an index in the back, and if you wanted to hop to a specific topic or chapter, you would reference the index in the back, find the page number and flip to that page.
This is far more efficient than skimming the entire book and searching for it manually. Indexes work in the same way. They create a special type of lookup table in the background that the database search engine can then use to retrieve data faster.
1. Querying on large amounts of data Anytime you deal with large sets of data (over 5,000 records), it's generally advised to use an index to retrieve the data faster. You can even build multiple search indexes on the same table to build different search criteria. For example, if you have both public and private data in the same table, you can build a "normal user" and an "administrator" search. On the whole, they are an incredible innovation that greatly improves database query response times. There are, however, a few caveats that are covered below.
2. Enforcing unique values (no duplicates) You can use a unique index to enforce unique values on field names.
Because of how quick indexes can make database queries, it's tempting to use them all the time, however, there are a few times when it's best to avoid them.
- Do not use an index on a table containing only a few records. They are most valuable when your record count gets into the 5 to 10k range.
- Indexes should not be used on fields that have or will have a high number of NULL values. since there is nothing to index on, it's a waste of database storage to do this.
- You can't index a field in an array/object, so keep that in mind when Designing your Database Model.
- If your field/column names are frequently changing, you should not index that field
- If a table is frequently used to add/edit data, It may be best not to index the table. While they speed up query speeds, they also slow the performance of adding/editing records given the nature of creating a duplicate lookup table behind the scenes.
You can access indexes by clicking on the "Indexes" menu item in the Database Table you want to index.
Implicit Indexes that come standard with every table
Xano comes out of the box with a few indexes that help keep things performant:
Primary: This creates an auto-incrementing integer for the primary id of the table. By default, looking up records by ID is already indexed.
GIN: is a special feature of the PostgreSQL database. GIN stands for Generalized Inverted Index. You don't need to do anything to set up this index, but just know it's used to index composite values such as documents and objects. This enables the expression builder to use the contains operator to search within objects.
Xano supports 4 different types of index
This is the standard index type that creates an index on a set of fields that you define.
This index helps to maintain data integrity to ensure that now rows of data have identical key values. When you create a unique index for a given field, Xano will throw an error if any duplicate values are added. It's worth noting that, unlike a primary key, unique index keys can accept NULL values
This extended index type allows you to index a spatial column with spatial data such as geometry or geography. The spatial index makes it possible to efficiently search for objects that overlap in space
Xano offers robust search capabilities, commonly referred to as fuzzy search, that you can utilize while querying records in a function stack. With the search index, you can set up This includes the normalization of words (ie. party vs parties), case-insensitive support, flexible expressions (words, phrases, and negations), and weighted priorities (ie. title vs description) for relevance. The search index is used to set up field priority and language.