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.