Announcement

Announcement Module
Collapse
No announcement yet.

speed problems with UNION

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • speed problems with UNION

    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;

    Comment


    • #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

      Comment

      Working...
      X