I have a table of 30 million+ records, that I need to search using wildcards. Each record is a 7-character reference, and I need to be able to, for example, return all records that start with A and the last three characters are BCD.
I have tried using like:
SELECT ref WHERE ref LIKE ‘A___BCD’
and also splitting the reference into seven separate fields and using equality:
SELECT ref WHERE first = ‘A’ AND fifth = ‘B’ AND sixth = ‘C’ AND seventh = ‘D’
I’ve tried both these, with and without indexes on the relevant fields, but the queries are taking upwards of five minutes. (
What is the answer? Would FULLTEXT work on single word fields? Or should I be using flat text files and grep? Or something else?
All advice gratefully received!