MySQL Forums
Forum List  »  Optimizer & Parser

UNIONs faster than simple joins
Posted by: Michael B Allen
Date: September 04, 2008 10:12AM

I have a case where UNION-ing a bunch of SELECT statements is actually faster than an equivalent statement that uses only simple joins. I'm happy with using UNION but if a longer more convoluted query is faster than the simple join type, I'm wondering if there are alternative methods that might speed things up.

Here's the long winded but faster query (~0.00 seconds):

SELECT l3.link_id, l3.link_a, l3.link_b
FROM px_link l3, px_link l2, px_link l1
WHERE l3.link_a = l2.link_a
AND l2.link_b = l1.link_a
AND l1.link_b = 72
UNION
SELECT l3.link_id, l3.link_a, l3.link_b
FROM px_link l3, px_link l2, px_link l1
WHERE l3.link_a = l2.link_a
AND l2.link_b = l1.link_b
AND l1.link_a = 72
UNION
SELECT l3.link_id, l3.link_a, l3.link_b
FROM px_link l3, px_link l2, px_link l1
WHERE l3.link_a = l2.link_b
AND l2.link_a = l1.link_a
AND l1.link_b = 72
UNION
SELECT l3.link_id, l3.link_a, l3.link_b
FROM px_link l3, px_link l2, px_link l1
WHERE l3.link_a = l2.link_b
AND l2.link_a = l1.link_b
AND l1.link_a = 72
UNION
SELECT l3.link_id, l3.link_a, l3.link_b
FROM px_link l3, px_link l2, px_link l1
WHERE l3.link_b = l2.link_a
AND l2.link_b = l1.link_a
AND l1.link_b = 72
UNION
SELECT l3.link_id, l3.link_a, l3.link_b
FROM px_link l3, px_link l2, px_link l1
WHERE l3.link_b = l2.link_a
AND l2.link_b = l1.link_b
AND l1.link_a = 72
UNION
SELECT l3.link_id, l3.link_a, l3.link_b
FROM px_link l3, px_link l2, px_link l1
WHERE l3.link_b = l2.link_b
AND l2.link_a = l1.link_a
AND l1.link_b = 72
UNION
SELECT l3.link_id, l3.link_a, l3.link_b
FROM px_link l3, px_link l2, px_link l1
WHERE l3.link_b = l2.link_b
AND l2.link_a = l1.link_b
AND l1.link_a = 72
ORDER BY 1

And here's the concise but slow query (~0.04 seconds):

SELECT l3.link_id, l3.link_a, l3.link_b
FROM px_link l3, px_link l2, px_link l1
WHERE (l3.link_a = l2.link_a AND l2.link_b = l1.link_a AND l1.link_b = 72)
OR (l3.link_a = l2.link_a AND l2.link_b = l1.link_b AND l1.link_a = 72)
OR (l3.link_a = l2.link_b AND l2.link_a = l1.link_a AND l1.link_b = 72)
OR (l3.link_a = l2.link_b AND l2.link_a = l1.link_b AND l1.link_a = 72)
OR (l3.link_b = l2.link_a AND l2.link_b = l1.link_a AND l1.link_b = 72)
OR (l3.link_b = l2.link_a AND l2.link_b = l1.link_b AND l1.link_a = 72)
OR (l3.link_b = l2.link_b AND l2.link_a = l1.link_a AND l1.link_b = 72)
OR (l3.link_b = l2.link_b AND l2.link_a = l1.link_b AND l1.link_a = 72)
GROUP BY l3.link_id
ORDER BY 1

If I break up the above query to use one UNION to eliminate the GROUP BY it's still slower.

There are no indexes on link_a or link_b. The px_link table is a simple mapping table that could potentially have millions of records so I wasn't sure if it should have indexes [for the curious - these queries find the third level of "links" between "entries"]. If I add indexes do you think that would make a big difference?

mysql> desc px_link;
+---------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------------+------+-----+---------+----------------+
| link_id | int(5) unsigned | NO | PRI | NULL | auto_increment |
| link_date | datetime | NO | | NULL | |
| link_a | int(5) unsigned | NO | MUL | NULL | |
| link_b | int(5) unsigned | NO | | NULL | |
| link_a_weight | float(6,3) | NO | | 1.000 | |
| link_b_weight | float(6,3) | NO | | 1.000 | |
+---------------+-----------------+------+-----+---------+----------------+
6 rows in set (0.05 sec)

Below is EXPLAIN output for each query respectively (hopefully this doesn't get mangled beyond recognition).

mysql> \. el3.sql
+----+--------------+------------------------+--------+---------------+--------+---------+--------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------------------+--------+---------------+--------+---------+--------------------+------+--------------------------+
| 1 | PRIMARY | l3 | ALL | link_a | NULL | NULL | NULL | 169 | |
| 1 | PRIMARY | l2 | ref | link_a | link_a | 4 | px.l3.link_a | 1 | Using index |
| 1 | PRIMARY | l1 | eq_ref | link_a | link_a | 8 | px.l2.link_b,const | 1 | Using index |
| 2 | UNION | l3 | ALL | link_a | NULL | NULL | NULL | 169 | |
| 2 | UNION | l2 | ref | link_a | link_a | 4 | px.l3.link_a | 1 | Using index |
| 2 | UNION | l1 | eq_ref | link_a | link_a | 8 | const,px.l2.link_b | 1 | Using index |
| 3 | UNION | l1 | index | link_a | link_a | 8 | NULL | 127 | Using where; Using index |
| 3 | UNION | l2 | ref | link_a | link_a | 4 | px.l1.link_a | 1 | Using index |
| 3 | UNION | l3 | ref | link_a | link_a | 4 | px.l2.link_b | 1 | |
| 4 | UNION | l1 | ref | link_a | link_a | 4 | const | 2 | Using index |
| 4 | UNION | l2 | ref | link_a | link_a | 4 | px.l1.link_b | 1 | Using index |
| 4 | UNION | l3 | ref | link_a | link_a | 4 | px.l2.link_b | 1 | |
| 5 | UNION | l3 | ALL | NULL | NULL | NULL | NULL | 169 | |
| 5 | UNION | l2 | ref | link_a | link_a | 4 | px.l3.link_b | 1 | Using index |
| 5 | UNION | l1 | eq_ref | link_a | link_a | 8 | px.l2.link_b,const | 1 | Using index |
| 6 | UNION | l3 | ALL | NULL | NULL | NULL | NULL | 169 | |
| 6 | UNION | l2 | ref | link_a | link_a | 4 | px.l3.link_b | 1 | Using index |
| 6 | UNION | l1 | eq_ref | link_a | link_a | 8 | const,px.l2.link_b | 1 | Using index |
| 7 | UNION | l3 | ALL | NULL | NULL | NULL | NULL | 169 | |
| 7 | UNION | l2 | index | link_a | link_a | 8 | NULL | 127 | Using where; Using index |
| 7 | UNION | l1 | eq_ref | link_a | link_a | 8 | px.l2.link_a,const | 1 | Using index |
| 8 | UNION | l1 | ref | link_a | link_a | 4 | const | 2 | Using index |
| 8 | UNION | l2 | ref | link_a | link_a | 4 | px.l1.link_b | 1 | Using index |
| 8 | UNION | l3 | ALL | NULL | NULL | NULL | NULL | 169 | Using where |
| NULL | UNION RESULT | <union1,2,3,4,5,6,7,8> | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort |
+----+--------------+------------------------+--------+---------------+--------+---------+--------------------+------+--------------------------+
25 rows in set (0.00 sec)

And EXPLAIN output for the simpler but slower query:

mysql> \. el3o.sql

+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------------------------------------------+
| 1 | SIMPLE | l1 | index | link_a | link_a | 8 | NULL | 169 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | l2 | ALL | link_a | NULL | NULL | NULL | 169 | Range checked for each record (index map: 0x2) |
| 1 | SIMPLE | l3 | ALL | link_a | NULL | NULL | NULL | 169 | Range checked for each record (index map: 0x2) |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------------------------------------------+
3 rows in set (0.01 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
UNIONs faster than simple joins
12560
September 04, 2008 10:12AM
3043
September 06, 2008 10:44PM
3202
September 09, 2008 12:16PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.