Re: union and missing limits

[M B]

My query is now working great! Even pagination is ok. Time increase is about 0.011 per 10 pages, that's satisfactory because there will not be A LOT of total pages. 60 maybe.
When I started this thread, query time was almost 1 sec.
After playing with indexes and adding limits on inner selects,
delay has dropped to 0.08.

The only problem now is getting total row count. Before I used simply count(*) but
that takes me now to the square one.

I think my solution is SQL_CALC_FOUND_ROWS and FOUND_ROWS.

But again, cannot find the right syntax for those to work correctly.
Not an easy task with those inner/outer selects and UNION.
I can only get 100 rows for total. In my original query it should be 1959.

Here's the simplified query now:

SELECT  user, color, piece, SUM(sum_quantity) AS quantity
    FROM ( 
     (
      SELECT  SQL_CALC_FOUND_ROWS *,
	      test1_loose.user,test1_loose.color,
              test1_loose.piece,sum(test1_loose.quantity) as sum_quantity
        FROM  test1_loose
	GROUP BY user, color, piece
        ORDER BY user
        LIMIT 0,50
        )
      UNION ALL (
      SELECT  test2_boxes.own_user,test3_boxinv.color,
              test3_boxinv.piece,
              sum(test3_boxinv.quantity * test2_boxes.own_quantity) AS sum_quantity
        FROM  test2_boxes,test3_boxinv
        WHERE  test2_boxes.own_item = test3_boxinv.item
	GROUP BY user, color, piece
        ORDER BY user
        LIMIT 0,50 
        )
      ) as uu
    GROUP BY user, color, piece
    ORDER BY user
    LIMIT 0,50

And the PHP part:

$grandtotal=mysql_result(mysql_query("SELECT FOUND_ROWS()"),0);