EXPLAIN EXTENDED

How to create fast database queries

Matching 3 of 4

Comments enabled. I *really* need your comment

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

Written by Quassnoi

April 17th, 2009 at 11:00 pm

Posted in MySQL

Leave a Reply