CODEWITHSUNDEEP
mysqlgrailsgrails-ormgrails-5
Pablo Pazos
Pablo Pazos

Reputation: 3216

How to define the index length for a domain attribute in Grails 5 with MySQL?

MySQL with InnoDB has a max index length it allows. If a String field in a Grails domain class has a maxSize: 2048 constraint, then an index on that field won't be created in MySQL, even if it's defined in the mapping DSL:

   static constraints = {
      archetypePath(maxSize: 2048)
   }

   static mapping = {

      tablePerHierarchy false

      archetypeId index: 'aid,aidpath'
      archetypePath index: 'aidpath'
   }

When I check my table, there is no index including the archetypePath column.

In MySQL SQL it's possible to define the index length to be smaller than the field used in the index, for instance:

CREATE TABLE t1 (
  col1 VARCHAR(10),
  col2 VARCHAR(4096),
  INDEX (col1, col2(10))
);

This table will have col2 with max length 4096 and the index will only consider the 10 first bytes of col2 for the index.

But in the Grails/GORM mapping DSL there is no way to actually do that, at least the documentation doesn't mention it.

I have successfully created the index on my domain table directly on the database, but I would like Grails to automatically generate the index when it creates the tables in the database.

From the docs (http://gorm.grails.org/latest/hibernate/manual/index.html#ormdsl):

To get the best performance out of your queries it is often necessary to tailor the table index definitions. How you tailor them is domain specific and a matter of monitoring usage patterns of your queries. With GORM’s DSL you can specify which columns are used in which indexes:

class Person { String firstName String address static mapping = { table 'people' version false id column: 'person_id' firstName column: 'First_Name', index: 'Name_Idx' address column: 'Address', index: 'Name_Idx,Address_Index' } }

Note that you cannot have any spaces in the value of the index attribute; in this example index:'Name_Idx, Address_Index' will cause an error.

UPDATE: This question is different than this other one since it's not for Grails 5: Specify index prefix length in Grails domain using InnoDB as datasource

Upvotes: 1

Views: 31

Answers (0)

Related Questions