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 = 500) 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 = 500) GROUP BY ol_i_id ORDER BY sumolqty DESC limit
50
----------------------------------------------------------------------------------------
Follow is the explain output:
-----------------------------------------------------------------------------------
1, 'PRIMARY', 'order_line', 'range',
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id', 'PRIMARY', '4', '', 13064,
'Using where; Using temporary; Using filesort'
1, 'PRIMARY', 'orders', 'eq_ref', 'PRIMARY', 'PRIMARY', '4',
'tpcw.order_line.ol_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'
----------------------------------------------------------------------------------------
Using temporary; Using filesort is bad ways.Could i optimize it via index
or rebuild the sql statement?
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]
