if you need to fetch multiple headlines, and you know their IDs? Should you use OR or IN(…)? Let’s find out what MySQL can tell us, using the lowest and highest headline IDs as well as one in between:
mysql> SELECT Url FROM Headline WHERE Id IN(1531513, 10231599, 13962322);
+———————————————-+
| Url |
+———————————————-+
| http://biz.yahoo.com/bond/010117/bf.html |
| http://biz.yahoo.com/e/021101/yhoo10-q.html |
| http://biz.yahoo.com/bw/030331/315850_1.html |
+———————————————-+
3 rows in set (0.00 sec)
mysql> EXPLAIN SELECT Url FROM Headline WHERE Id IN(1531513, 10231599, 13962322) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Headline
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 3
Extra: Using where
1 row in set (0.00 sec)
mysql> SELECT Url FROM Headline WHERE Id = 1531513 OR Id = 10231599 OR Id = 13962322;
+———————————————-+
| Url |
+———————————————-+
| http://biz.yahoo.com/bond/010117/bf.html |
| http://biz.yahoo.com/e/021101/yhoo10-q.html |
| http://biz.yahoo.com/bw/030331/315850_1.html |
+———————————————-+
3 rows in set (0.03 sec)
mysql> EXPLAIN SELECT Url FROM Headline WHERE Id = 1531513 OR Id = 10231599 OR Id =
13962322 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Headline
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 3
Extra: Using where
1 row in set (0.00 sec)
Both queries execute very quickly, and their EXPLAIN output is the same. They’re functionally the same. It’s clear that either query may return anywhere from zero to three rows. We’re querying based on a unique index (the primary key), so there isn’t much for MySQL to think about. As it turns out, we happen to know that in this case, MySQL internally changed the multi-OR query to one that uses a single IN(…) list. However, it’s clear that as the number of IDs increases, the query string will be smaller if you use the IN(..). A smaller query means less parsing overhead and better performance.
