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 = 'foo@bar.com' 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='foo@bar.com' && phone='555-1212') || 
  (email='test@test.com' && phone='888-1212') || 
   ...

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

eggyal · Accepted Answer · 2012-11-17T00:20:25.253

33

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

SELECT * FROM email_phone_notes WHERE (email, phone) IN (
  ('foo@bar.com'  , '555-1212'),
  ('test@test.com', '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 'foo@bar.com' AS email, '555-1212' AS phone
UNION ALL
  SELECT 'test@test.com', '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
  ('foo@bar.com'  , '555-1212'),
  ('test@test.com', '888-1212')
  -- etc.
;

SELECT * FROM email_phone_notes NATURAL JOIN foo;

edited Nov 17 '12 at 00:20

answered Nov 17 '12 at 00:07

eggyal

122,705
18
212
237

2

Could you explain why the first option is not "index-friendly"? Sources? – Magnus O. Nov 22 '18 at 07:55
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". – barwnikk Aug 17 '21 at 23:23

score 3 · Answer 2 · answered Nov 17 '12 at 00:10

3

You can use a row constructor like this:

SELECT *
FROM email_phone_notes
WHERE (email, phone) IN (
  ('foo@bar.com', '555-1212'),
  ('test@test.com', '888-1212')
)

SQLfiddle example

answered Nov 17 '12 at 00:10

AndreKR

32,613
18
106
168

1

Sadly won't use the index, though. – eggyal Nov 17 '12 at 00:12
1

Curse you, MySQL. Well, the original one seems neither: http://sqlfiddle.com/#!2/86c1e/2 – AndreKR Nov 17 '12 at 00:16
Bear in mind that an index would not be used on that table anyway, as it's too small (fewer than 10 records). – eggyal Nov 17 '12 at 00:16
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. – Newtang Nov 17 '12 at 00:31

score 0 · Answer 3 · answered Mar 09 '21 at 10:38

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.

How to bulk SELECT rows with multiple pairs in WHERE clause

3 Answers3

Linked