Grails multi column indexes

Question

Can someone explain how to define multi column indexes in Grails? The documentation is at best sparse.

This for example does not seem to work at all: http://grails.org/GORM+Index+definitions

I've had some luck with this, but the results seems random at best. Definitions that works in one domain class does not when applied to another (with different names of course). http://www.grails.org/doc/1.1/guide/single.html#5.5.2.6%20Database%20Indices

Some working examples and explanations would be highly appreciated!

Answer 1

To make your index multi-column, list the columns with comma separator (note, no space after the comma, to avoid this bug. The second URL you point to hits the bug, as it says:

index:'Name_Idx, Address_Index'

with a space; it should work as

index:'Name_Idx,Address_Index'

The first URL you point to was a proposed change (I don't believe it's implemented currently and have no idea how likely it is to ever be).

Answer 2

I needed to be able to control the order of the columns in my multi-column index and also make it unique. I worked around the GORM / Hibernate limitations by creating the index in Bootstrap using direct SQL:

class BootStrap {

    DataSource dataSource

    def init = { servletContext ->
        if (!MyModel.count()) { // new database
            createIndexes()
            ...
        }
    }

    private void createIndexes() {
        Sql sql = new Sql(dataSource)
        sql.execute("create unique index my_index on my_model(col1,col2);")
    }

Answer 3

The solution that has worked for me for multi-column indexes is:

class ClassName {
    String name
    String description
    String state

    static mapping = {
        name index: 'name_idx'
        description index: 'name_idx'
        state index: 'name_idx'
    }
}

This creates an index called 'name_idx' with the three columns in the index.

Downside: the columns are listed in the index in alphabetical order, not the order that they were entered.

Answer 4

AFAIK, the index closure shown here was never implemented, so those examples should be ignored (this page is for discussing possible implementations, rather than documenting an actual implementation).

The correct way to define a single-column index name_idx for a name property is

static mapping = {
      name index:'name_idx'
}

Sorry, but I don't know how to define a multi-column index, try the Grails mailing list if you don't get an answer here. In the unlikely event that multi-column indices can't be declared directly in the domain classes, you could define them in an SQL file which creates them if they don't already exist (or drops and re-creates them). This SQL file could be executed by the init closure in Bootstrap.groovy