I have a query that could be resolved 99% of the time using an index but needs a table scan for a few queries:
SELECT * FROM mytable WHERE idx=123 OR name like "%foo%" limit 1
The execution time is way above the regular index lookup even in the cases when an item is found and EXPLAIN confirms that MySQL 5.7 is always doing a table scan.
I tried to add a
FORCE INDEX(primary), but that doesn’t help.
Unfortunately I this needs to be done in a single query.
Any ideas how to speed up the queries where an item is found in the index ?