All we need is an easy explanation of the problem, so here it is.
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
Logically there can only be one match and the
LIMIT 1 there lets MySQL know it can stop searching when it has found one. So MySQL could stop evaluating the query when it has an index match, same as it does when doing the
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 (executed from within an app that only allows a single query).
Any ideas how to speed up the queries where an item is found in the index?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
The trick is to force the Optimizer to avoid evaluating both sides of
(I do not know if there are simpler ways, but I feel pretty sure this will work.)
id is the
SELECT * FROM mytable WHERE id = IF ( EXISTS( SELECT id FROM mytable WHERE idx = 123 ), ( SELECT id FROM mytable WHERE idx = 123 LIMIT 1 ), ( SELECT id FROM mytable WHERE name LIKE "%foo%" LIMIT 1 ) );
Try the next query (I assume that
mytable.idx is indexed):
( SELECT * FROM mytable WHERE idx=123 LIMIT 1) UNION ALL ( SELECT * FROM mytable WHERE name like '%foo%' AND NOT EXISTS ( SELECT NULL FROM mytable WHERE idx=123 ) LIMIT 1) LIMIT 1;
I ended up using a stored function:
DELIMITER $$ CREATE FUNCTION my_lookup(n VARCHAR(255)) RETURNS VARCHAR(255) DETERMINISTIC BEGIN DECLARE ret VARCHAR(255); SELECT val INTO ret FROM mytable WHERE idx = n; IF (ret IS NULL) THEN SELECT val INTO ret FROM mytable WHERE name LIKE CONCAT("%", n, "%") limit 1; END IF; RETURN (ret); END $$ DELIMITER ;
That does the minimal searching needed.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂