MySQL - Index on suffix of string

Question

I am storing email addresses in a column as varchar and I will be making queries to select emails based on the the suffix (the domain name). So for example:

---------------
EMAIL 
---------------
[email protected]
[email protected]
[email protected]
[email protected]

I want to be able to efficiently select all emails with "aol.com" suffix, for example – and there could eventually be millions of records matching the query. Is there an efficient way to do this, or would it be better to just create another column and store just the suffix and add an index to that column?

You already know the answer to this: create another column and index it. — user1864610
– user1864610, Commented Dec 21, 2014 at 19:10
I already know that is viable solution yes, but I would rather do it with the existing column if an efficient solution exists. — Zack
– Zack, Commented Dec 21, 2014 at 19:12
mySql does not provide an indexing-function for this sort of search (you might try FULLTEXT index though - I don't know if it performs well and if it uses @ and . as word separators). Also partitioning will not work with this sort of criteria. The most efficient way would be to create a seperate column. — Chris
– Chris, Commented Dec 21, 2014 at 19:19

Gordon Linoff · Accepted Answer · 2014-12-21 23:58:15Z

2

MySQL allows indexes on prefixes of strings, that are of fixed length. That doesn't quite help you, but it comes sort of close.

You can also try a full-text search on the column. You want to be sure that @ is not considered a word character (hence it is a separator) and probably that . is considered a word character. If you do this, you can use match against on the domain.

However, it is probably easiest to add a new column and assign the value and index:

alter table t add column domain varchar(255);

update t
    set domain = substring_index(email, '@', -1);

create index idx_t_domain on t(domain);

answered Dec 21, 2014 at 23:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL - Index on suffix of string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related