Force MySQL index on OR query?

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 LIKE part.

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.

Method 1

The trick is to force the Optimizer to avoid evaluating both sides of OR.

(I do not know if there are simpler ways, but I feel pretty sure this will work.)

If id is the PRIMARY KEY:

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 )
                  );
                

Method 2

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;

Method 3

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply