Indexing problem

[aniltc]

hi all


I am indexing email addresses, and I want to be able to search by domain name. I can’t use a regular index for this, I would need to to a query like:

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

That query would, of course, not use indexes.

I have heard about MySQL needs its own built-in calculated indexes. The ability to create an index based on some expression.

So how can we solve this problem ?

[r937]

indexes based on some expression? not to my knowledge

do yourself a favour, add another column to your table, and populate it with the domain

then you can index and search on that

[aniltc]

can we use triggers ?

[r937]

you can, but you don't need to

[malluwood]

can we use triggers ?

yes you can if you use MySql 5

see this

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


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);

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

cooooooooooooool