Jeremy Cole

The Problem

You need the contents of your index to differ from the data you are indexing. For example, you’re indexing email addresses, and you want to be able to search by domain name. You can’t use a regular index for this, because you would need to to a query like:

SELECT ... FROM users WHERE email LIKE '%.com'

That query would, of course, not use indexes.

The Real Solution

MySQL needs its own built-in calculated indexes. The ability to create an index based on some expression, in our example, on REVERSE(email), and have MySQL automatically figure out how to use it. Maybe some day that will exist in MySQL, but it doesn’t currently.

The Temporary Solution for MySQL 5.0

You can use Triggers in MySQL 5.0 to automatically maintain another column, which is then indexed! You’ll want to create your table like so:

CREATE TABLE users (
  id        INT       NOT NULL auto_increment,
  name      CHAR(50)  NOT NULL,
  email     CHAR(120) NOT NULL,
  r_email   CHAR(120) NOT NULL,
  PRIMARY KEY (id),
  INDEX (email),
  INDEX (r_email)
);

Notice that I added an extra column, r_email to store the reversed email address. You can then create a couple of triggers to keep the reversed email address up to date automatically:

CREATE TRIGGER users_r_email_in
  BEFORE INSERT ON users
  FOR EACH ROW
    SET NEW.r_email = REVERSE(NEW.email);

CREATE TRIGGER users_r_email_up
  BEFORE UPDATE ON users
  FOR EACH ROW
    SET NEW.r_email = REVERSE(NEW.email);

Ta da! Whenever you need to search by domain, just run your query like so:

SELECT ... FROM users WHERE r_email LIKE 'moc.%'

Or, you could go for the minimal amount of modification of your client code, and use this query instead:

SELECT ... FROM users WHERE r_email LIKE REVERSE('%.com')

Indexes will be used!