Database Indexes

GORM allows create database index with tag index, uniqueIndex, those indexes will be created when AutoMigrate or CreateTable with GORM

Index Tag

GORM accepts lots of index settings, like class, type, where, comment, expression, sort, collate, option

Check the following example for how to use it

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"`
}

uniqueIndex

tag uniqueIndex works similar like index, it equals to index:,unique

type User struct {
  Name1 string `gorm:"uniqueIndex"`
  Name2 string `gorm:"uniqueIndex:idx_name,sort:desc"`
}

Composite Indexes

Use same index name for two fields will creates composite indexes, for example:

// create composite index `idx_member` with columns `name`, `number`
type User struct {
  Name   string `gorm:"index:idx_member"`
  Number string `gorm:"index:idx_member"`
}

Fields Priority

The column order of a composite index has an impact on its performance so it must be chosen carefully

You can specify the order with the priority option, the default priority value is 10, if priority value is the same, the order will be based on model struct’s field index

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

Shared composite indexes

If you are creating shared composite indexes with an embedding struct, you can’t specify the index name, as embedding the struct more than once results in the duplicated index name in db.

In this case, you can use index tag composite, it means the id of the composite index. All fields which have the same composite id of the struct are put together to the same index, just like the original rule. But the improvement is it lets the most derived/embedding struct generates the name of index by NamingStrategy. For example:

type Foo struct {
  IndexA int `gorm:"index:,unique,composite:myname"`
  IndexB int `gorm:"index:,unique,composite:myname"`
}

If the table Foo is created, the name of composite index will be idx_foo_myname.

type Bar0 struct {
  Foo
}

type Bar1 struct {
  Foo
}

Respectively, the name of composite index is idx_bar0_myname and idx_bar1_myname.

composite only works if not specify the name of index.

Multiple indexes

A field accepts multiple index, uniqueIndex tags that will create multiple indexes on a field

type UserIndex struct {
  OID          int64  `gorm:"index:idx_id;index:idx_oid,unique"`
  MemberNumber string `gorm:"index:idx_id"`
}