Case Sensitivity and Indices in MYSQL

Question

I am creating a system where is use e-mail address as unique identifier. There are times I need to look for a user by the e-mail address. If the user enters the e-mail address in all lower case, but the database has it stored as mixed case, is it a full scan, or will the database still use the index?

This really is a problem because I attempt to validate that the e-mail address is valid when adding the user to the system.

I am using grails with MYSQL on the back end for the database. I am currently doing this to find the user

def c = User.createCriteria()
def currentUser = c.get() { ilike('emailAddress',message.sender.address) }

I know I can force the case at the UI, but I would also like to force it at the model level

Thanks, and sorry for the long question

Answer 1

Unfortunately MySQL does not support function based indexes like Postgres and Oracle. (Source)

A possible workaround in MySQL is to add another column for lower case e-mail addresses, and a trigger that populates it with lower case e-mails on all updates and inserts. Then simply index that column, and use that for your lookups.

With a function based index, you would have been able to do the following:

CREATE INDEX 
    ix_users
ON 
    table_users
USING 
    lower(email_address);

Answer 2

MySQL specifies collation for every character column, which may be case-sensitive or case-insensitive.

Index is built using whatever collation is specified on the column, so:

1. Alter your table to specify case-insensitive collation on email column (like ascii-general-ci, for example).
2. Rebuild your index.
3. Enjoy.

Keep in mind that all queries against email will now be case-insensitive.

Answer 3

With Grails you have a few options to validate the model:

You can write a setter for the emailAddress that converts it to a consistent case:

public void setEmailAddress(email){ 
    emailAddress = email
}

A more involved but correct answer would be to create a custom editor (PropertySupportEditor) that will handle the normalization for you automatically.

You will also would want to write a custom validator to ensure that Grails' validation fails if the emailAddress is not correctly normalized. If you wanted to make it really elegant you could make the validator into a reusable constrtaint using the constraints plugin which could result in something like this:

static constraints = {
    emailAddress(normalizedEmail:true)
}