EXPLAIN EXTENDED

From Stack Overflow:

Say I have a query like this:

SELECT  *
FROM    my_table
WHERE   name = "john doe"
        AND phone = "8183321234"
        AND email = "johndoe@yahoo.com"
        AND address = "330 some lane"

But say I only need 3 conditions out of the 4 to match.

I know I can write a very long query with several OR‘s, but I was wondering if there was a feature for this?

A nice and elegant solution proposed by Tomalak says:

SELECT  *
FROM    my_table
WHERE   CASE WHEN name = "john doe"           THEN 1 ELSE 0 END +
        CASE WHEN phone = "8183321234"        THEN 1 ELSE 0 END +
        CASE WHEN email = "johndoe@yahoo.com" THEN 1 ELSE 0 END +
        CASE WHEN address = "330 some lane"   THEN 1 ELSE 0 END
        >= 3

, but, as noted by the author, it does not use the indexes.

Since the values seem to be quite selective, it will be better to use indexes on them.

Let’s create sample tables:

Table creation details

CREATE TABLE filler (
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
) ENGINE=Memory;

CREATE TABLE t_info (
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(200) NOT NULL,
        phone VARCHAR(20) NOT NULL,
        email VARCHAR(200) NOT NULL,
        address VARCHAR(200) NOT NULL,
        KEY ix_info_name (name),
        KEY ix_info_phone (phone),
        KEY ix_info_email (email),
        KEY ix_info_address (address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER $$

CREATE PROCEDURE prc_filler(cnt INT)
BEGIN
        DECLARE _cnt INT;
        SET _cnt = 1;
        WHILE _cnt <= cnt DO
                INSERT
                INTO    filler
                SELECT  _cnt;
                SET _cnt = _cnt + 1;
        END WHILE;
END
$$

DELIMITER ;

START TRANSACTION;
CALL prc_filler(1000000);
COMMIT;

INSERT
INTO    t_info (id, name, phone, email, address)
SELECT  id,
        CONCAT(
        CASE FLOOR(RAND(20090417) * 10)
             WHEN 0 THEN 'Alice'
             WHEN 1 THEN 'Bob'
             WHEN 2 THEN 'Chris'
             WHEN 3 THEN 'Dave'
             WHEN 4 THEN 'Eve'
             WHEN 5 THEN 'Frank'
             WHEN 6 THEN 'Gill'
             WHEN 7 THEN 'Hugh'
             WHEN 8 THEN 'Ian'
             WHEN 9 THEN 'Jon'
        END,
        ' ',
        CASE FLOOR(RAND(20090417 << 1) * 10)
             WHEN 0 THEN 'Angela'
             WHEN 1 THEN 'Bracco'
             WHEN 2 THEN 'Chianese'
             WHEN 3 THEN 'de Matteo'
             WHEN 4 THEN 'Enzo'
             WHEN 5 THEN 'Falco'
             WHEN 6 THEN 'Gandolfini'
             WHEN 7 THEN 'Hadria'
             WHEN 8 THEN 'Imperioli'
             WHEN 9 THEN 'James'
        END
        ),
        CONCAT('+155580000', 10 + FLOOR(RAND(20090417 << 2) * 90)),
        CONCAT(FLOOR(RAND(20090417 << 3) * 100), '@example.com'),
        CONCAT(FLOOR(RAND(20090417 << 4) * 100), ' North Lane')
FROM    filler;

Here we have 100 distinct values on each of the fields (except phone, which has 90).

The straightforward query:

SELECT  *
FROM    t_info t
WHERE   CASE WHEN name  = 'Eve Chianese' THEN 1 ELSE 0 END +
        CASE WHEN phone = '+15558000042' THEN 1 ELSE 0 END +
        CASE WHEN email = '42@example.com' THEN 1 ELSE 0 END +
        CASE WHEN address = '42 North Lane' THEN 1 ELSE 0 END
        >= 3

uses full table scan:

EXPLAIN EXTENDED
SELECT  *
FROM    t_info t
WHERE   CASE WHEN name  = 'Eve Chianese' THEN 1 ELSE 0 END +
        CASE WHEN phone = '+15558000042' THEN 1 ELSE 0 END +
        CASE WHEN email = '42@example.com' THEN 1 ELSE 0 END +
        CASE WHEN address = '42 North Lane' THEN 1 ELSE 0 END
        >= 3

and runs for more than 3 seconds.

To enable indexes we need:

1. Make a range scan of each of the conditions
2. UNION ALL the resultsets (it’s UNION ALL the resultsets, not UNION all the resultsets!)
3. COUNT(*) the matches grouped by id
4. Filter out those id‘s with COUNT(*) < 3
5. Join with the original table

Here's the query:

SELECT  i.*
FROM    (
        SELECT  id, COUNT(*)
        FROM    (
                SELECT  id
                FROM    t_info t
                WHERE   name  = 'Eve Chianese'
                UNION ALL
                SELECT  id
                FROM    t_info t
                WHERE   phone = '+15558000042'
                UNION ALL
                SELECT  id
                FROM    t_info t
                WHERE   email = '42@example.com'
                UNION ALL
                SELECT  id
                FROM    t_info t
                WHERE   address = '42 North Lane'
                ) q
        GROUP BY
                id
        HAVING  COUNT(*) >= 3
        ) dq
JOIN    t_info i
ON      i.id = dq.id

This runs much faster (0.15 seconds) and of course uses all four indexes:

EXPLAIN EXTENDED
SELECT  i.*
FROM    (
        SELECT  id, COUNT(*)
        FROM    (
                SELECT  id
                FROM    t_info t
                WHERE   name  = 'Eve Chianese'
                UNION ALL
                SELECT  id
                FROM    t_info t
                WHERE   phone = '+15558000042'
                UNION ALL
                SELECT  id
                FROM    t_info t
                WHERE   email = '42@example.com'
                UNION ALL
                SELECT  id
                FROM    t_info t
                WHERE   address = '42 North Lane'
                ) q
        GROUP BY
                id
        HAVING  COUNT(*) >= 3
        ) dq
JOIN    t_info i
ON      i.id = dq.id

As was proposed by Brent Baisley, it's actually possible to make this query more simple by using INDEX MERGE:

EXPLAIN EXTENDED
SELECT  *
FROM    t_info t
WHERE   (
        name  = 'Eve Chianese'
        OR phone = '+15558000042'
        OR email = '42@example.com'
        OR address = '42 North Lane'
        )
        AND
        CASE WHEN name  = 'Eve Chianese' THEN 1 ELSE 0 END +
        CASE WHEN phone = '+15558000042' THEN 1 ELSE 0 END +
        CASE WHEN email = '42@example.com' THEN 1 ELSE 0 END +
        CASE WHEN address = '42 North Lane' THEN 1 ELSE 0 END
        >= 3

Unfortunately, as I already mentioned in one of the previos articles, INDEX MERGE implementation is not very efficient in MySQL, that's why this query is more slow than that with UNION ALL:

SELECT  *
FROM    t_info t
WHERE   (
        name  = 'Eve Chianese'
        OR phone = '+15558000042'
        OR email = '42@example.com'
        OR address = '42 North Lane'
        )
        AND
        CASE WHEN name  = 'Eve Chianese' THEN 1 ELSE 0 END +
        CASE WHEN phone = '+15558000042' THEN 1 ELSE 0 END +
        CASE WHEN email = '42@example.com' THEN 1 ELSE 0 END +
        CASE WHEN address = '42 North Lane' THEN 1 ELSE 0 END
        >= 3

Summary: for a query that requries some of the indexed values to match, it's better to use UNION ALL of single matches and filter by COUNT(*)'ing the matched records.