Speed ​​up LIKE queries without literal language

Question

I have a table with 3 columns VARCHAR. I want to make a query to search if an alphanumeric string satisfies these 3 columns. For example : Similar to "%whatever%", but faster.

I've seen FULLTEXT indexing used and it works well, but only if I have the words used in natural language. However, these 3 columns contain codes, references.

Example: Use keyword 1234 to find reference REF12345.

Do you have any ideas?

Answer 1

If you added indexes and need faster speed -> try using DB HINTS (not all vendors have this feature but mysql does) or you can try using cache

Mysql Tip: https://dev.mysql. com/doc/refman/8.0/en/optimizer-hints.html

Good article about hints: https://ologicalread.com/mysql -query-hints-improve-performance-mc12/

Mysql cache: https://dev.mysql. com/doc/refman/5.7/en/query-cache.html