NO effect :-(
From: pow <[EMAIL PROTECTED]>
To: 王 旭 <[EMAIL PROTECTED]>
Subject: Re: optimize a sql statement
Date: Mon, 18 Jul 2005 11:51:23 +0800
Do u have composite index on order_line.ol_o_id AND order_line.ol_i_id?
You could try that...
王 旭 wrote:
> 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
>
_________________________________________________________________
与联机的朋友进行交流，请使用 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]
