Good time..

The database of 800 thousand records. Search by word (you) works tight. The sample goes on the fields

..... and ( advert.text LIKE '%тест%' or advert.name_adv LIKE '%тест%' or advert.key_words LIKE '%тест%' )

Tell me, do I need to add indexes for these three columns (for the main columns, we added earlier and the work has accelerated)?

If so, which ones: composite or individually or selectively out of three?

    1 answer 1

    Normal indexes for the query type LIKE '% word%' are meaningless, they will not be used anyway. Try looking towards full-text indexes.

    • @ Akina doesn’t have to be so categorical, you’ve seen that in 5.7 Like it uses indices - Vadim
    • No, did not see. The maximum that I saw is the facts, which suggest that 5.7 instead of table fullscan learned to do index fullscan + index merge. And then, provided that the index has not yet washed out of the cache. - Akina
    • then you have to believe EXPLAIN .... on sql.ru I showed. - Vadim