# speed problems with UNION

**URL:** <https://forums.percona.com/t/speed-problems-with-union/1379>\
**Category:** Other MySQL® Questions\
**Created:** [April 8, 2010, 11:36am UTC](https://forums.percona.com/t/speed-problems-with-union/1379 "2010-04-08T11:36:00Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![mabog](https://avatars.discourse-cdn.com/v4/letter/m/71e660/32.png) [@mabog](https://forums.percona.com/u/mabog)\
**Post date:** [April 8, 2010, 11:36am UTC](https://forums.percona.com/t/speed-problems-with-union/1379/1 "2010-04-08T11:36:00Z")

</div>

Hello!  
Query below is slow because sub-selects are without limits.  
I can’t figure out the right syntax to get correct result. Can someone help me to add limits so that the result is the same with/without limits (exept row count of course).  
Limits are used for pagination.

If that is impossible with UNION, could query be changed to use JOINs insted UNION ?

Thanks!

SELECT user, color, piece, SUM(quantity) AS quantity FROM ( SELECT test1\_loose.user,test1\_loose.color, test1\_loose.piece,test1\_loose.quantity FROM test1\_loose UNION SELECT test2\_boxes.own\_user,test3\_boxinv.color, test3\_boxinv.piece, (test3\_boxinv.quantity \* test2\_boxes.own\_quantity) AS quantity FROM test2\_boxes,test3\_boxinv WHERE test2\_boxes.own\_item = test3\_boxinv.item ) as ss GROUP BY user, color, piece ORDER BY user LIMIT 0,50

Result:

±-----±------±--------±---------+| user | color | piece | quantity |±-----±------±--------±---------+| 9 | 99 | piece13 | 11300 || 9 | 66 | piece19 | 9200 || 9 | 99 | piece14 | 200 || 9 | 22 | piece15 | 300 || 9 | 44 | piece16 | 2200 || 4 | 99 | piece33 | 12300 || 4 | 77 | piece13 | 12600 |±-----±------±--------±---------+

Tables:

CREATE TABLE `test1_loose` ( `id` int(11) NOT NULL auto\_increment, `piece` varchar(255) default NULL, `user` int(11) default NULL, `quantity` int(11) default NULL, `color` int(11) default NULL, `log` varchar(255) default NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO\_INCREMENT=11 DEFAULT CHARSET=latin1 AUTO\_INCREMENT=11 ;## Dumping data for table `test1_loose`#INSERT INTO `test1_loose` VALUES (1, ‘piece13’, 9, 9100, 99, NULL);INSERT INTO `test1_loose` VALUES (2, ‘piece19’, 9, 9200, 66, NULL);# --------------------------------------------------------## Table structure for table `test2_boxes`#CREATE TABLE `test2_boxes` ( `id` int(11) NOT NULL auto\_increment, `own_user` int(11) default NULL, `own_item` varchar(255) default NULL, `own_quantity` int(11) default NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO\_INCREMENT=4 DEFAULT CHARSET=latin1 AUTO\_INCREMENT=4 ;## Dumping data for table `test2_boxes`#INSERT INTO `test2_boxes` VALUES (1, 9, ‘box1’, 1);INSERT INTO `test2_boxes` VALUES (2, 9, ‘box2’, 1);INSERT INTO `test2_boxes` VALUES (3, 4, ‘box4’, 3);# --------------------------------------------------------## Table structure for table `test3_boxinv`#CREATE TABLE `test3_boxinv` ( `id` int(11) NOT NULL auto\_increment, `item` varchar(255) default NULL, `piece` varchar(255) default NULL, `color` int(11) default NULL, `quantity` int(11) default NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO\_INCREMENT=9 DEFAULT CHARSET=latin1 AUTO\_INCREMENT=9 ;## Dumping data for table `test3_boxinv`#INSERT INTO `test3_boxinv` VALUES (1, ‘box1’, ‘piece13’, 99, 100);INSERT INTO `test3_boxinv` VALUES (2, ‘box1’, ‘piece14’, 99, 200);INSERT INTO `test3_boxinv` VALUES (3, ‘box1’, ‘piece15’, 22, 300);INSERT INTO `test3_boxinv` VALUES (4, ‘box2’, ‘piece13’, 99, 2100);INSERT INTO `test3_boxinv` VALUES (5, ‘box2’, ‘piece16’, 44, 2200);INSERT INTO `test3_boxinv` VALUES (6, ‘box3’, ‘piece14’, 88, 3100);INSERT INTO `test3_boxinv` VALUES (7, ‘box4’, ‘piece33’, 99, 4100);INSERT INTO `test3_boxinv` VALUES (8, ‘box4’, ‘piece13’, 77, 4200);

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [April 17, 2010, 11:26pm UTC](https://forums.percona.com/t/speed-problems-with-union/1379/2 "2010-04-17T23:26:43Z")

</div>

Quoting the MySQL manual,

To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. The following example uses both clauses:

(SELECT a FROM t1 WHERE a=10 AND B=1)  
UNION  
(SELECT a FROM t2 WHERE a=11 AND B=2)  
ORDER BY a LIMIT 10;

---

<div class="post-metadata">

**Author:** ![Troy](https://avatars.discourse-cdn.com/v4/letter/t/85f322/32.png) [@Troy](https://forums.percona.com/u/Troy)\
**Post date:** [April 20, 2010, 12:05pm UTC](https://forums.percona.com/t/speed-problems-with-union/1379/3 "2010-04-20T12:05:47Z")

</div>

That won’t solve this particular problem - the LIMIT applies to the GROUP.

The second statement in the UNION joins two tables on a non-primary, non-indexed column. As a matter of fact, the column is VARCHAR(255) and is nullable so that’s where I’d start optimizing. There should be an item table somewhere and the box quantities should reference it (probably using the id column since the current tables all have one).

Next, the UNION (both statements) could be ordered by the user, color and piece as per the example above. The GROUP BY in the outer statement will try to order them that way eventually. An index would be helpful here but if there were an item table containing these columns it would be even better.

How many records are you dealing with here and how long is it taking?

Troy