I have a table of employees (roughly 4.5 million) which have columns called
domain LIKE '%shetlandfoods.co.uk'
OR domain LIKE '%example1.co.uk'
OR domain LIKE '%example2.co.uk'
-- About 50 additional domains in this list
OR domain LIKE '%example50.co.uk
AND (job_title LIKE '%Manager%' OR job_title LIKE '%Director%')
AND (job_title NOT LIKE '%Assistant%')
Here are two options which come to mind (the second suggested by @paul above in his comment).
One is that you could pre process the data in the
domain column and use a
WHERE clause looking something like the following:
WHERE domain IN ('shetlandfoods.co.uk', 'alac.shetland.co.uk', 'malakofflimited.co.uk', ...)
Another option might be to compare the reverse of the domain against the reverse of the terms you had in your original
WHERE clause, e.g.
WHERE REVERSE(domain) LIKE 'ku.oc.sdoofdnaltehs%' OR REVERSE(domain) LIKE 'ku.oc.dnaltehs.cala%' OR REVERSE(domain) LIKE 'ku.oc.detimilffokalam%' OR ...
You could even store the reverse of the
domain from your app/UI layer so that you don't have to force MySQL to compute the reverse for each term in the
I might lean towards the first option, assuming you have the bandwidth to extract out the domains before hitting MySQL.