MySQL Forums
Forum List  »  Newbie

union and missing limits
Posted by: M B
Date: April 08, 2010 01:02AM

Need little more help with this UNION query.
Didn't understand at first why my query is so slow, but the reason was that my page added LIMIT only at the end of the query.
Like this:
GROUP BY user, color, piece ORDER BY user LIMIT 0,50

But it does need LIMITs in the inner SELECTs too to get results fast.
Query lasted 1 second (15000 records), but without outer select 0.005 !!

My problem is that I can't find the right syntax to make that query to work.
I think it's only about where to put those aliases! I get this error:
"Every derived table must have its own alias"

I have tried something like this with this example:
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
        ORDER BY user
        LIMIT 0,50
        ) as s1
      UNION ALL (
      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
        ORDER BY user
        LIMIT 0,50 
        ) as s2
      ) as uu
    GROUP BY user, color, piece ORDER BY user LIMIT 0,50

But that gives a syntax error.



Edited 1 time(s). Last edit at 04/08/2010 12:56AM by M B.

Options: ReplyQuote


Subject
Written By
Posted
M B
March 27, 2010 04:49AM
March 27, 2010 10:35AM
M B
March 27, 2010 11:45AM
March 28, 2010 12:33PM
M B
March 28, 2010 02:53PM
union and missing limits
M B
April 08, 2010 01:02AM
M B
April 08, 2010 01:41AM
April 08, 2010 07:51AM
M B
April 08, 2010 09:11AM
April 09, 2010 12:33AM
M B
April 09, 2010 03:59AM
April 09, 2010 09:42AM
M B
April 09, 2010 01:06PM
April 09, 2010 09:41PM
M B
April 12, 2010 03:48AM
April 08, 2010 07:56AM
April 08, 2010 08:46AM


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.