Fictional table "cities" for example only with the following fields
id|name|visibility|key_1|key_2|key_3
So let's insert the city of "São Paulo" as a registry, in this case we will have two key words that obviously will be removed accents (programatically) and everything small.
key_1 = sao
key_2 = paulo
In this way a search in a table with millions of records would look like this:
select name from cities
where visibility = 1
and (
key_1 = 'sao' or
key_1 = 'paulo' or
key_2 = 'sao' or
key_2 = 'paulo'
);
The question is: will it even be faster than Full text or wildcards LIKE %%? is it a good practice?
I have seen large companies using this technique, however, I would like your opinion.
Thank you in advance!