Creating table schema with pre-defined keywords

1
Hello, I am creating a table schema that needs to be optimized, the reason for this being tables that will have 3 to 5 million records. The big concern here is with regard to search, in this case the famous LIKE %% ... can not even pass close, because of this at the time of each insertion, 3 key words will be created based on the main field as a name of something , example:

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!

    
asked by anonymous 23.10.2017 / 05:46

0 answers