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!