Follow is my sql statement:
-------------------------------------------------------------------------------------
SELECT SUM(ol_qty) sumolqty, ol_i_id FROM orders, order_line
WHERE orders.o_id = order_line.ol_o_id AND orders.o_id > (SELECT
MAX(o_id)-10000 FROM orders)
AND NOT (order_line.ol_i_id = 5000) AND orders.o_c_id IN (SELECT o_c_id
FROM orders, order_line
WHERE orders.o_id = order_line.ol_o_id and
orders.o_id > (SELECT MAX(o_id)-10000 FROM orders) AND order_line.ol_i_id =
5000)
GROUP BY ol_i_id ORDER BY sumolqty DESC limit 50
--------------------------------------------------------------------------------------
follows are explain output:
--------------------------------------------------------------------------------------
1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 19398,
'Using where; Using temporary; Using filesort'
1, 'PRIMARY', 'order_line', 'ref',
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4',
'tpcw.orders.o_id', 1, 'Using where'
3, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id',
'orders_o_c_id', '5', 'func', 1, 'Using where; Using index'
3, 'DEPENDENT SUBQUERY', 'order_line', 'ref',
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'order_line_ol_o_id', '4',
'tpcw.orders.o_id', 1, 'Using where'
4, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'
2, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'
------------------------------------------------------------------------------------------
This sql statement performance is too bad.Please help me to optimize it .
thanks!
_________________________________________________________________
免费下载 MSN Explorer: http://explorer.msn.com/lccn/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
