mySQL: OR, UNION and UNION ALL

I was doing some SQL tuning on mySQL today and I learned a few interesting things.

Let’s take an example of a simple query where we have an OR of two mutually exclusive conditions, each on indexed columns.

SELECT *
FROM   t1
WHERE  user_id = 1
OR     location_id = 2;

In mySQL there is a feature called an Index Merge that, as the name suggests, runs a scan of the two indexes and merges the results together. Kinda neat.

I guess it’s not disimilar to what Oracle does with bitmap conversions, but it looks a little less scary in the middle of a massive plan than something like this. :).

-----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |    20 |   260 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | T1               |    20 |   260 |     6   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                  |       |       |            |          |
|   3 |    BITMAP OR                     |                  |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | T1_LOCATION_ID_I |       |       |     1   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|                  |       |       |            |          |
|*  7 |      INDEX RANGE SCAN            | T1_USER_ID_I     |       |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

As it turned out, the query I was tuning was too complicated to take advantage of an index merge, so I tried converting it to use a UNION ALL. Remember, the conditions are mutually exclusive, so there was no need to worry about duplicates. Taking the example of the previous query, it would look something like this.

SELECT *
FROM   t1
WHERE  user_id = 1
UNION ALL
SELECT *
FROM   t1
WHERE  location_id = 2;

The real query I was tuning performed much better with a UNION ALL, but I thought I better check to see if mySQL had any gotchas associated with UNION and UNION ALL. It turns out that both UNION and UNION ALL can create internal temporary tables to get the job done. I suppose it’s not that different to Oracle doing sorts in memory or pushing them out to disk, but it did kinda freak me out when I first read it. :)

Although I could never see myself devoting that much time to another engine, it is quite interesting seeing how they get the job done.

Cheers

Tim…