Monday, September 24, 2012

Comparing multiple columns in MySQL

Few days ago, I needed to write an SQL query on combined unique key. The situation is that I have a table with one unique constraint on two columns. Let's call those columns A, and B. The table had these values:

+---+---+
| A | B |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 2 | 4 |
+---+---+

At first, I tried this query below:

SELECT * FROM tbl WHERE (A, B) IN ((1, 1))

It worked beautifully. MySQL EXPLAIN command said it only needed to look at one row.

So I ventured further to put in one more value to search for:

SELECT * FROM tbl WHERE (A, B) IN ((1, 1), (1, 2))

This time, EXPLAIN told me it needed to scan the whole table (4 rows). I thought it was odd and tried to make a comparison with the regular query:

SELECT * FROM tbl WHERE (A=1 AND B=1) OR (A=1 AND B=2)

The third query turned out to scan only two rows!

Perhaps the IN operator has not been optimized in this case yet so for now I've got to do with a clumsier query. I could, of course, shorten the third query to A=1 AND B IN (1, 2) to make it less wieldy.

On a related note, SQLite does not even support tuple comparison.

No comments:

Post a Comment