speed problems with UNION

MySQL & MariaDB Other MySQL® Questions
1

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);

2

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;

3

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