Now,I make this sql statement to easy.
Follow is the sql statement:
-------------------------------------------------------------------------
SELECT ol_i_id FROM orders,order_line
WHERE order_line.ol_o_id = orders.o_id
GROUP BY ol_i_id
-------------------------------------------------------------------------
Follow is the explain output:
-------------------------------------------------------------------------
1, 'SIMPLE', 'orders', 'index', 'PRIMARY', 'PRIMARY', '4', '', 259231,
'Using index; Using temporary; Using filesort'
1, 'SIMPLE', 'order_line', 'ref', 'PRIMARY,order_line_ol_o_id', 'PRIMARY',
'4', 'tpcw.orders.o_id', 1, ''
-------------------------------------------------------------------------
Can it be optimized?
From: 王 旭 <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Subject: optimize a sql statement
Date: Sat, 16 Jul 2005 18:24:15 +0800
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]
_________________________________________________________________
与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
