CODEWITHSUNDEEP
sqlgrailsindexinggrails-orm
Mikey
Mikey

Reputation: 4742

Does grails/SQL automatically know which indices to use?

I have a simple domain class with two properties. For example:

class Person {
  String firstName
  String lastName
}

Now I want to use the dynamic finders to find a guy named "Some Guy":

def thisGuy = Person.findByFirstNameAndLastName("Some","Guy")

What should I add to my person class to create the right indices in the database to make this search go quickly? This is my current guess:

static mapping = {

    firstName index: 'Name_Idx'
    lastName  index: 'Name_Idx'
}

Is that correct, or should I make an index for each?

static mapping = {
    firstName index: 'Fname_Idx'
    lastName index: 'Lname_Idx'
}

Upvotes: 0

Views: 252

Answers (1)

Tiago Farias
Tiago Farias

Reputation: 3407

If you use:

static mapping = {
        firstName index: 'name_idx'
        lastName index: 'name_idx'
        description index: 'name_idx'
}

this will create 3 columns in the index name_idx.

Remember the columns will be listed in alphabetical order.

If you mean, "what is best: multiple indexes or multi-column indexes?" Well, it depends a lot. But, all in all, this post will put you in the right path. The answer you are looking for is that it depends on the where clauses of your most used queries and your grouping.

Upvotes: 3

Related Questions