CODEWITHSUNDEEP
mysqldatabaseperformanceemailindexing
Serge
Serge

Reputation: 1601

REVERSE() email addresses before storing/indexing, or not?

A db table will store lots of email addresses in one of the columns. There will be often lookups of individual addresses by that column, so I'll set an index on that column. Only full addresses are going to be searched for, no parts or LIKE's.

Which makes more sense, in terms of performance: to store addresses reversed, i.e. 'moc.liamg@tset' or keep them straight, i.e. '[email protected]'?

Upvotes: 3

Views: 590

Answers (1)

Tomalak
Tomalak

Reputation: 338316

If you regularly query for partial email domains (i.e. the part after the @), reversing could make some sense in an indexed column.

This way the DB engine can use the index with queries like this one:

... WHERE ReverseMail LIKE REVERSE('.com') + '%'

If you query for full domains most of the time, just create a separate column that contains the domain only and index this one. Index lookups against an = operator will be faster than those against a LIKE operator.

Upvotes: 1

Related Questions