Use when creating single or composite database indexes to improve query performance by allowing the database to find rows faster, including unique indexes, partial indexes, expression indexes, or database-specific index options.
GORM allows creating database indexes with tags index and uniqueIndex. Indexes are created during AutoMigrate or CreateTable.
GORM accepts many index settings: class, type, where, comment, expression, sort, collate, option.
type User struct {
Name string `gorm:"index"`
Name2 string `gorm:"index:idx_name,unique"`
Name3 string `gorm:"index:,sort:desc,collate:utf8,type:btree,length:10,where:name3 != 'jinzhu'"`
Name4 string `gorm:"uniqueIndex"`
Age int64 `gorm:"index:,class:FULLTEXT,comment:hello \\, world,where:age > 10"`
Age2 int64 `gorm:"index:,expression:ABS(age)"`
}
// MySQL option
type User struct {
Name string `gorm:"index:,class:FULLTEXT,option:WITH PARSER ngram INVISIBLE"`
}
// PostgreSQL option
type User struct {
Name string `gorm:"index:,option:CONCURRENTLY"`
}
The uniqueIndex tag works like index and equals index:,unique:
type User struct {
Name1 string `gorm:"uniqueIndex"`
Name2 string `gorm:"uniqueIndex:idx_name,sort:desc"`
}
Note: This does not work for unique composite indexes (see below).
Use the same index name for multiple fields to create composite indexes:
// Create composite index `idx_member` with columns `name`, `number`
type User struct {
Name string `gorm:"index:idx_member"`
Number string `gorm:"index:idx_member"`
}
For a unique composite index:
// Create unique composite index `idx_member` with columns `name`, `number`
type User struct {
Name string `gorm:"index:idx_member,unique"`
Number string `gorm:"index:idx_member,unique"`
}
Column order impacts performance. Use priority to control order (default is 10). Lower priority values come first:
type User struct {
Name string `gorm:"index:idx_member"`
Number string `gorm:"index:idx_member"`
}
// column order: name, number
type User struct {
Name string `gorm:"index:idx_member,priority:2"`
Number string `gorm:"index:idx_member,priority:1"`
}
// column order: number, name
type User struct {
Name string `gorm:"index:idx_member,priority:12"`
Number string `gorm:"index:idx_member"`
}
// column order: number, name
When embedding a struct multiple times, explicit index names cause duplicates. Use composite to let GORM generate names via NamingStrategy:
type Foo struct {
IndexA int `gorm:"index:,unique,composite:myname"`
IndexB int `gorm:"index:,unique,composite:myname"`
}
// Table Foo gets index: idx_foo_myname
type Bar0 struct {
Foo
}
// Table Bar0 gets index: idx_bar0_myname
type Bar1 struct {
Foo
}
// Table Bar1 gets index: idx_bar1_myname
Note: composite only works when no explicit index name is specified.
A field can have multiple index or uniqueIndex tags:
type UserIndex struct {
OID int64 `gorm:"index:idx_id;index:idx_oid,unique"`
MemberNumber string `gorm:"index:idx_id"`
}
| Setting | Example | Description |
|---|---|---|
index | gorm:"index" | Basic index |
index:name | gorm:"index:idx_name" | Named index |
uniqueIndex | gorm:"uniqueIndex" | Unique index |
unique | gorm:"index:idx_name,unique" | Unique constraint |
type | gorm:"index:,type:btree" | Index type (btree, hash, etc.) |
class | gorm:"index:,class:FULLTEXT" | Index class |
sort | gorm:"index:,sort:desc" | Sort order |
collate | gorm:"index:,collate:utf8" | Collation |
length | gorm:"index:,length:10" | Prefix length |
where | gorm:"index:,where:active = true" | Partial index condition |
comment | gorm:"index:,comment:my comment" | Index comment |
expression | gorm:"index:,expression:ABS(age)" | Expression index |
option | gorm:"index:,option:CONCURRENTLY" | Database-specific option |
priority | gorm:"index:idx,priority:1" | Column order in composite |
composite | gorm:"index:,composite:myname" | Shared composite index |
WHERE, JOIN, or ORDER BY clauses.is_active column is often not useful, as the index will not be selective enough for the optimizer to use it.TEXT or BLOB columns can create huge indexes and is generally not effective. Use full-text search engines instead.