is there a "best way" to short circuit a mysql query

Question

I have a situation where I'm assembling a query based on user provided criteria and want to figure out what the most efficient way is to do this.

If I have a table that looks like this:

int id | varchar phone | varchar email | varchar RFID

and the user will pass in an array which defines the order (and items) with which they'd like to look up a user which *could look like this:

["id","email","phone"]

or it could look like this:

["email"]

or it could look like this:

["phone","rfid"]

or any other possible combination of those 4 fields.

Based on what I receive I need to look the user up in the order in which these fields arrived and if I find a match, I don't want to keep looking.

In other words if the input is

["email","rfid","phone"]

and I look into the db and find a user with the provided email, I don't want to keep looking to see if their rfid also matches, I just want to return said user. However, if I don't find such an email, then I want to move on to the rfid.

So, in the various tests I've done (mostly playing with a case statement in the where clause) my results have been really terrible. Frequently taking almost a second to return a value, as opposed to taking <50ms when I simplify the where to search for the individual field.

I should note that all these fields are indexed.

So... my question is, should I just bite the bullet and make as many sql calls as there are items in the incoming array, or is there some really efficient way to structure a single query that will not bog down the system as my various attempts have.

I recognize that this may be too abstract a question, but am hoping that there's some mechanism for just such a use that I'm simply overlooking.

Answer 1

I don't think there's any good way to do a short-circuit in SQL. You can construct a WHERE clause that uses OR to combine the critiera, but doing this generally prevents it from using the indexes. You can use a UNION like this:

SELECT * FROM
    (SELECT 1 precedence, table.*
     FROM table
     WHERE field1 = 'value'
     UNION
     SELECT 2 precedence, table.*
     FROM table
     WHERE field2 = 'value'
     ...
    ) x
ORDER BY precedence
LIMIT 1

where you replace field1, field2, etc. with the field names from the input array. This will produce the desired results in one query, but it will have to perform all the sub-queries, it won't short-circuit.

The best solution is probably to solve it in the application code. Loop through the fields in the input, and perform a query for just that field. When you get a result, break out of the loop and return it.