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