1. Index Concept and Application

1.1 What is an Index

An index is a data structure used in a database management system to speed up data retrieval operations. It can be seen as a "directory" in the database, allowing for the quick location of data in a data table, avoiding full table scans, and significantly improving query efficiency. In practical applications, the correct use of indexes can greatly enhance the performance of a database.

1.2 Types of Indexes

There are various types of indexes, each with its own design and optimization for different application scenarios:

  • Single-Field Index: An index that includes only one field, suitable for scenarios that rely on a single condition for quick queries.
  • Composite Index: An index that includes multiple fields, providing optimization for queries that contain these fields.
  • Unique Index: Ensures the uniqueness of index fields, disallowing the existence of duplicate values. Unique indexes can be single-field or composite.

2. Index Definition

2.1 Field Index Definition

Creating a single-field index involves establishing an index on a specific column of a data table, which can be achieved using the Fields method. The following example demonstrates how to create an index on the phone field of the User entity.

func (User) Fields() []ent.Field {
    return []ent.Field{
        field.String("phone"),
    }
}
func (User) Indexes() []ent.Index {
    // Create a single-field index.
    return []ent.Index{
        index.Fields("phone"),
    }
}

In this code snippet, the phone field is indexed. This allows the system to utilize the index for faster searches when querying the phone field.

2.2 Unique Index Definition

A unique index ensures the uniqueness of data on the indexed columns. It can be created by adding the Unique method to the field. The following examples illustrate creating unique indexes for single and multiple fields.

Creating a unique index for a single field:

func (User) Fields() []ent.Field {
    return []ent.Field{
        field.String("email").Unique(),
    }
}

In this example, the email field is specified as unique, ensuring that each user's email is unique in the database.

Creating a unique index for a combination of multiple fields:

func (User) Indexes() []ent.Index {
    return []ent.Index{
        // Create a unique index for multiple fields.
        index.Fields("first_name", "last_name").Unique(),
    }
}

This code defines a composite unique index for the combination of first_name and last_name fields, preventing the occurrence of records with the same values in both fields.

2.3 Definition of Composite Index

When the query conditions involve multiple fields, a composite index can come into play. The composite index stores data in the order defined in the index. The order of the index has a significant impact on query performance, so when defining a composite index, the order of the fields needs to be determined based on the query pattern.

Here is an example of creating a composite index:

func (User) Indexes() []ent.Index {
    return []ent.Index{
        // Create a composite index with multiple fields.
        index.Fields("country", "city"),
    }
}

In this example, a composite index is created on the country and city fields. This means that when performing query operations involving these two fields, the database can quickly locate the data that meets the conditions.

A composite index not only speeds up query performance but also supports index-based sorting operations, providing more efficient data retrieval performance. When designing a composite index, it is common to place the fields with higher selectivity at the front of the index so that the database optimizer can better utilize the index.

3. Edge Indexes

In the ent framework, edge indexes are a way to define indexes through edges (relationships). This mechanism is typically used to ensure the uniqueness of fields under specific relationships. For example, if your database model includes cities and streets, and each street name under a city needs to be unique, edge indexes can be utilized to achieve this.

// The file ent/schema/street.go defines the schema of the Street entity.
type Street struct {
    ent.Schema
}

func (Street) Fields() []ent.Field {
    // Define fields
    return []ent.Field{
        field.String("name"),
    }
}

func (Street) Edges() []ent.Edge {
    // Define the edge relationship with City, where Street belongs to a City.
    return []ent.Edge{
        edge.From("city", City.Type).
            Ref("streets").
            Unique(),
    }
}

func (Street) Indexes() []ent.Index {
    // Create a unique index through the edge to ensure the uniqueness of street names within the same city.
    return []ent.Index{
        index.Fields("name").
            Edges("city").
            Unique(),
    }
}

In this example, we create a Street entity and associate it with the City entity. By defining an edge index in the Indexes method of the Street entity, we ensure that the name of each street under a city is unique.

Chapter 5: Advanced Index Options

5.1 Full-Text and Hash Indexes

Full-text index and hash index are two unique index types in MySQL and PostgreSQL, and they are used for different query optimization scenarios.

Full-text index is commonly used for searching text data, especially when you need to perform complex searches, such as word matching searches. Both MySQL and PostgreSQL databases support full-text indexing. For example, in MySQL, you can define a full-text index like this:

// The file ent/schema/user.go defines the schema of the User entity
func (User) Indexes() []ent.Index {
    // Create a full-text index using the FULLTEXT category in MySQL
    return []ent.Index{
        index.Fields("description").
            Annotations(entsql.IndexTypes(map[string]string{
                dialect.MySQL: "FULLTEXT",
            })),
    }
}

Hash index is particularly suitable for equality queries and does not support sorting and range queries. In PostgreSQL, you can use a hash index like this:

func (User) Indexes() []ent.Index {
    // Define an index of type HASH
    return []ent.Index{
        index.Fields("c4").
            Annotations(entsql.IndexType("HASH")),
    }
}

5.2 Partial Indexes and Index Prefixes

Partial index is a type of index that indexes only the rows in a table that satisfy specific conditions. In SQLite and PostgreSQL, you can use the WHERE clause to create partial indexes.

For example, defining a partial index in PostgreSQL:

func (User) Indexes() []ent.Index {
    // Create a partial index on the "nickname" field, containing only rows where "active" is true
    return []ent.Index{
        index.Fields("nickname").
            Annotations(
                entsql.IndexWhere("active"),
            ),
    }
}

Index prefix is particularly useful for text fields, especially in MySQL. It can shorten the index creation time, reduce the space occupied by the index, and also provide good performance. As shown below, for MySQL, you can define an index with a prefix:

func (User) Indexes() []ent.Index {
    // Create an index using index prefix
    return []ent.Index{
        index.Fields("description").
            Annotations(entsql.Prefix(128)),
    }
}

5.3 Index Annotations and Customization

In ent, Annotations is a feature that allows developers to customize indexes. You can define the index type, set sorting rules, and more.

For example, the following code demonstrates how to specify the column sorting rules in an index:

func (User) Indexes() []ent.Index {
    return []ent.Index{
        // Use annotations to define the column sorting rules of the index
        index.Fields("c1", "c2", "c3").
            Annotations(entsql.DescColumns("c1", "c2")),
    }
}

With the annotation feature, developers can flexibly customize indexes to optimize database performance and structure.