Speeding up searches for (sub)domains

In a column with e-mail addresses, searching for all addresses of a given domain is a non-trivial task performance-wise. Given a typical table structure, the only way to find all addresses @gmail.com is to use a LIKE query with a wildcard:

SELECT * FROM clients WHERE email LIKE '[e-mail]';

Of course, storing the address and domain parts in separate columns would solve this particular problem. But as soon as you were asked for a quick way to find all clients with an e-mail address from a British provider, you would be out of luck again, resorting to:

SELECT * FROM clients WHERE maildomain LIKE '%.co.uk';

Both queries would cause a full table scan because no index can support the wildcard at the beginning of the search term.

In this recipe, you will be given a simple but effective approach to enable the use of indexes for both of the problems just presented. Notice that you will need to make minor adjustments to the queries sent against...