Grails - How To Create Custom Table Index Or Composite Index

May 30, 2014 Snippet comments

By default, Grails will create the necessary indexes on primary keys and foreign keys in all domain classes. However, we sometimes need to create additional indexes for performance purposes.

Default Index

Grails automatically creates tindexes on primary keys and foreign keys.
The domain class Category below will have 1 index automatically created by Grails on start up of the application.

class Category {
  String name
  String description
}

This will create the table category with columns id, version, name and description. By default, Grails will create a column id as your primary key, and column version for optimistic locking. An index on id will also be created.
The domain class Item below will have 2 indexes:

class Item {
  String name
  Category cat
  String sku
  String internalCode
}

This will create the table item with columns id, version, cat_id, sku, and internal_code. Index on id and cat_id will automatically created, because the former is a primary key while the latter is a foreign key.

Custom Index

If you need to create an index on column name from the domain class Item, below should be the changes:

class Item {
  String name
  Category cat
  String sku
  String internalCode
  static mapping = {
    name index: 'item_name_idx'
  }
}

This will create an index with equivalent SQL code to:

CREATE INDEX item_name_idx ON item (name);

Custom Composite Index

If you need a composite index, below is an example on how to combine sku and internalCode into a single index:

class Item {
  String name
  Category cat
  String sku
  String internalCode
  static mapping = {
    name index: 'item_name_idx'
    sku index: 'item_sku_ic_idx'
    internalCode index: 'item_sku_ic_idx'
  }
}

This will create the indexes with equivalent SQL code to:

CREATE INDEX item_name_idx ON item (name);
CREATE INDEX item_sku_ic_idx ON item (sku, internal_code);