How to bulk SELECT rows with multiple pairs in WHERE clause

Question

Let's say I have a table, email_phone_notes that looks like this:

+-----------------------+--------------+------+-----+---------+-------+
| Field                 | Type         | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| email                 | varchar      | NO   | PRI | NULL    |       |
| phone                 | varchar      | NO   | PRI | NULL    |       |
| notes                 | text         | NO   |     | 0       |       |
+-----------------------+--------------+------+-----+---------+-------+

So, each email/phone combination is unique, but you could have several email addresses with different phone numbers and vice versa. This is a little contrived but it mirrors my scenario.

I'd like to do a query like this:

SELECT * FROM email_phone_notes  WHERE email = '[email protected]' AND phone = '555-1212';

But, I'd like to do multiple pairs at once so I don't have to make several SELECT queries. It's also important to keep the pairs together because I don't want to return an errant phone/email combination that wasn't requested.

I could do something like this, but for the possibility of several hundred values the query will be really long.

SELECT * FROM email_phone_notes WHERE ( 
  (email='[email protected]' && phone='555-1212') || 
  (email='[email protected]' && phone='888-1212') || 
   ...

Is there a more elegant solution, or should I stick with this?

Unaccustomed

Answer 1

If you're after elegant SQL, you could use row constructors:

SELECT * FROM email_phone_notes WHERE (email, phone) IN (
  ('[email protected]'  , '555-1212'),
  ('[email protected]', '888-1212')
  -- etc.
);

However, that's not at all index-friendly and would not be recommended on a table of any significant size. Instead, you could materialise a table with your desired pairs and join that with your table:

SELECT * FROM email_phone_notes NATURAL JOIN (
  SELECT '[email protected]' AS email, '555-1212' AS phone
UNION ALL
  SELECT '[email protected]', '888-1212'
-- etc.
) t;

Or else pre-populate a (temporary) table:

CREATE TEMPORARY TABLE foo (PRIMARY KEY (email, phone)) Engine=MEMORY
  SELECT email, phone FROM email_phone_notes WHERE FALSE
;

INSERT INTO foo
  (email, phone)
VALUES
  ('[email protected]'  , '555-1212'),
  ('[email protected]', '888-1212')
  -- etc.
;

SELECT * FROM email_phone_notes NATURAL JOIN foo;

Lupita

Could you explain why the first option is not "index-friendly"? Sources? – Estell

I tested the first option and in MySQL Workbench I don't see "full-scan", so where in doesn't make a full scan if u have an index on (email, phone). In "Statement Analysis" I see normal values in rows sent / rows scanned, etc. Maybe it's not index-friendly, because it firstly search first record, then second, etc.? So, we've O(n) where n is number of elements in "IN". – Purr

Answer 2

You can use a row constructor like this:

SELECT *
FROM email_phone_notes
WHERE (email, phone) IN (
  ('[email protected]', '555-1212'),
  ('[email protected]', '888-1212')
)

SQLfiddle example

Arithmetic

Sadly won't use the index, though. – Lupita

Curse you, MySQL. Well, the original one seems neither: sqlfiddle.com/#!2/86c1e/2 – Arithmetic

Bear in mind that an index would not be used on that table anyway, as it's too small (fewer than 10 records). – Lupita

Interesting. I didn't know you could construct a WHERE IN that way. Any idea why an index isn't used? If I do WHERE email IN (%values%) an index is used there. – Unaccustomed

Answer 3

Earlier answers are provided almost 10 years ago. It's 2020 now and the answer seems to have changed.

Short answer: Use row constructor syntax - the optimizer may (or may not) use an index.

In this example here, the optimizer is able to use the index, even when the row constructor doesn't cover the prefix of the index.

Herod