Marriadb index for left wildcard statement

Question

I have been scratching my head on this problem for days. It shouldn't be this hard yet i haven't found the answer yet.

My data looks as following: +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | name | varchar(255) | NO | | NULL | | | email | varchar(255) | NO | MUL | NULL | | | ip | varchar(255) | NO | | NULL | | | username| varchar(255) | NO | | NULL | | +---------+--------------+------+-----+---------+-------+

Now the next query will bypass the index because the wildcard is at the start of the LIKE statement.

SELECT * FROM db WHERE email LIKE '%@hotmail.com';

Now this query will use index because indexes start in the beginning

SELECT * FROM db WHERE email LIKE 'first.last@%';

Now for the question: There has to be a method to create a index that starts on the right side so that i can use indexes for statements such as: "LIKE %@hotmail.com" without having to create a second column.

Answer 1

Regular B-tree indexes always index a string value from left-to-right. There's no option to make them index right-to-left.

One workaround that I've seen is to make another column to store a copy of the string, reversed, and then index that.

MariaDB and MySQL 5.7 also have virtual columns. You can index virtual columns. So you could define a virtual column as the REVERSE() of your regular email column, and index it.

• https://mariadb.com/kb/en/mariadb/virtual-computed-columns/
• https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

In your case, if you have the need to search separately for email name and email domain, you might store those two components of the email address in separate columns, and index each one.