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:
Read the rest of this entry »