Optimizing MySQL LIKE Query with Pattern Matching for Large Data(20M+ records) and FULL TEXT SEARCH not giving accurate results

Optimizing MySQL LIKE Query with Pattern Matching for Large Dataset (20M+ records)

I'm struggling with performance issues in MySQL while searching through a large table containing over 20 million records. My current query using LIKE operator is timing out:

SELECT * FROM my_table WHERE columnName LIKE '%key%';

Current Setup:

• Database: MySQL
• Table Size: ~20 million records
• Search Pattern Requirements:
  • '%keyword%' (contains)
  • 'keyword%' (starts with)
  • '%keyword' (ends with)
• Search keywords can include:
  • Numbers
  • Alphabets
  • Special characters (- and _)

What I've tried:

1. Implemented FULLTEXT SEARCH:

ALTER TABLE my_table ADD FULLTEXT(columnName);
SELECT * FROM my_table WHERE MATCH(columnName) AGAINST('*keyword*' IN BOOLEAN MODE);

2. Configured ngram parser:

ngram_token_size=3

Issues Faced:

• FULLTEXT SEARCH doesn't provide accurate results for all keyword patterns
• Regular LIKE queries are too slow
• Need to maintain accuracy while improving performance with FULL TEXT SEARCH as well
• ngram=3 is slowing the query for few keywords

Questions:

1. How can I tune FULLTEXT SEARCH to achieve accurate results similar to LIKE '%keyword%'?
2. Are there any alternative approaches or indexing strategies for this use case?
3. What would be the optimal configuration for ngram parser to handle all these patterns?

Any help on optimizing this search while maintaining accuracy would be greatly appreciated.