sorry,my english is poor.
Follow is my mean.
One,I create a index.(create index orders_test on orders(o_id,o_c_id))
Two,I do explain again.
Follow is result
------------------------------------------------------------------
1, 'PRIMARY', 'orders', 'range', 'PRIMARY,orders_test', 'orders_test', '4',
'', 517890, 'Using where; Using index; Using temporary; Using filesort'
1, 'PRIMARY', 'order_line', 'ref',
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
2, 'DEPENDENT SUBQUERY', 'orders', 'ref',
'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1,
'Using where; Using index'
2, 'DEPENDENT SUBQUERY', 'order_line', 'ref',
'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'
------------------------------------------------------------------
Is the performance of sql statement be increased?
From: Sebastian <[EMAIL PROTECTED]>
To: 王 旭 <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED], mysql@lists.mysql.com, [EMAIL PROTECTED]
Subject: Re: optimize a sql statement
Date: Fri, 22 Jul 2005 01:47:24 -0400
wow consider a different font when posting.. gives me a headache reading
that.
王 旭 wrote:
> I analyzed the query plan again.
> ---------------------------------------------------------------
> 1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '',
> 348660, 'Using where; Using temporary; Using filesort'
> 1, 'PRIMARY', 'order_line', 'ref',
> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
> 2, 'DEPENDENT SUBQUERY', 'orders', 'ref',
> 'PRIMARY,orders_o_c_id','orders_o_c_id', '5', 'func', 1, 'Using where;
> Using index'
> 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref',
> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
> 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'
> ---------------------------------------------------------------------
> In the plan,i find there are 348660 row scan in table orders.And in
> the sql statement,orders.o_id is be used to join operation and
> orders.o_c_id be used in the where statement.So i create a index
> "orders_test" on table orders(o_id and o_c_id).
> After create the index.I execute the explain again.Follow is the
result:
> -----------------------------------------------------------------------
> 1, 'PRIMARY', 'orders', 'range', 'PRIMARY,orders_test', 'PRIMARY',
> '4', '', 348660, 'Using where; Using temporary; Using filesort'
>
> 1, 'PRIMARY', 'order_line', 'ref',
> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
>
> 2, 'DEPENDENT SUBQUERY', 'orders', 'ref',
> 'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1,
> 'Using where; Using index'
>
> 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref',
> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
>
> 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'
>
----------------------------------------------------------------------------
>
>
> I find the index "order_test" be set in the possible_keys but not set
> in the keys.
> So i alter the sql statement with "use index" statement.
> Follow is the explain output after alter:
>
----------------------------------------------------------------------------
>
>
> 1, 'PRIMARY', 'orders', 'range', 'orders_test', 'orders_test', '4',
> '', 519210, 'Using where; Using index; Using temporary; Using filesort'
>
> 1, 'PRIMARY', 'order_line', 'ref',
> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
>
> 2, 'DEPENDENT SUBQUERY', 'orders', 'ref',
> 'PRIMARY,orders_o_c_id,orders_test', 'orders_o_c_id', '5', 'func', 1,
> 'Using where; Using index'
>
> 2, 'DEPENDENT SUBQUERY', 'order_line', 'ref',
> 'PRIMARY,order_line_ol_i_id,order_line_ol_o_id,order_line_test',
> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, 'Using where'
>
> 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized away'
>
---------------------------------------------------------------------------------
>
>
> Performance maybe much more bad.
> Now i have two question.
> One,Are there any probability for optimization performance using this
> way?
> Two,I am not able to calculate the cost time with the explain output.I
> have learned the knowlege about "7.2.2. Estimating Query Performance"
> in help.But i can't understand.
>
> thanks
>
>> From: 王 旭 <[EMAIL PROTECTED]>
>> To: [EMAIL PROTECTED]
>> CC: mysql@lists.mysql.com, [EMAIL PROTECTED]
>> Subject: Re: optimize a sql statement
>> Date: Thu, 21 Jul 2005 18:46:32 +0800
>>
>> Thank you SGreen.But i can'optimize the sql statement like your way
>> because this query must describe in one sql statement.So i think i
>> maybe optimize this sql statement through creating high efficiency
>> index or describe this sql statement in other method. Rewrite this
>> sql statement must in one sql statement.
>>
>> Follow is the original sql statement:
>> -----------------------------------------------------
>> SELECT ol_i_id
>> FROM orders, order_line
>> WHERE orders.o_id = order_line.ol_o_id and o_id>0 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 SUM(ol_qty)DESC limit 5
>> -------------------------------------------------------------
>> Follow is the original explain output:
>>
>> --------------------------------------------------------------
>> 1, 'PRIMARY', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '',
>> 519414, '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'
>>
>> 2, 'DEPENDENT SUBQUERY', 'orders', 'ref', 'PRIMARY,orders_o_c_id',
>> 'orders_o_c_id', '5', 'func', 1, 'Using where; Using index'
>>
>> 2, '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'
>>
>> 3, 'SUBQUERY', '', '', '', '', '', '', , 'Select tables optimized
away'
>>
-------------------------------------------------------------------------------
>>
>
>
>>
>>
>> In the mail,Pow give me a way.He advise me to build a index on
>> orders.o_id and orders.o_c_id.I understanding his mean is table
>> orders join table order_line using the index.Because orders.o_c_id be
>> limited in the where statemetn,so the index should be filter date
>> before Join operation.In this way,the index of orders using much less
>> data to join the table order_line.But i can't see the effect when i
>> do like it.I don't know the reason.
>>
>> Are you have any better way?
>>
>>
>>> From: [EMAIL PROTECTED]
>>> To: 王 旭 <[EMAIL PROTECTED]>
>>> CC: mysql@lists.mysql.com,[EMAIL PROTECTED]
>>> Subject: Re: optimize a sql statement
>>> Date: Tue, 19 Jul 2005 16:10:18 -0400
>>>
>>> 王 旭 <[EMAIL PROTECTED]> wrote on 07/18/2005 03:12:28 AM:
>>>
>>> > 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!
>>> > > >>
>>>
>>> Your original query, reformatted only:
>>>
>>> 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
>>>
>>> If I try to describe your query, this is how it reads to me:
>>>
>>> You are want to get for each order, how many individual items
comprises
>>> that order. You only want to see the top 50 item counts from within
the
>>> 10000 most recent orders ignoring those line items where the ol_i_id
=
>>> 5000 but including those where the o_c_id is equal to that of an
order
>>> that has a line item where ol_i_id = 5000.
>>>
>>> For starters, you could pre-compute the lowest o_id and use that
value
>>> from a variable instead of getting it during execution (yes it will
>>> be a
>>> scalar and only computed once any way.... just bear with me...)
>>>
>>> SELECT @min_o_id := max(o_id)-10000 from orders;
>>>
>>> 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 > @min_o_id
>>> 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 > @min_o_id
>>> AND order_line.ol_i_id = 5000
>>> )
>>> GROUP BY ol_i_id
>>> ORDER BY sumolqty DESC
>>> limit 50
>>>
>>> Just doing that eliminates two subqueries, simplifying your total
query
>>> execution plan. If I rewrite the query to use explicit JOINs and
>>> eliminated the subqueries, your query turns into:
>>>
>>> /* q1: limit queries to the last 10000 orders */
>>> SELECT @min_o_id := max(o_id)-10000 from orders;
>>>
>>> /*q2: locate all customers within the last 10000 orders who
>>> have at least 5000 order_lines in a single order */
>>> CREATE TEMPORARY TABLE tmpOCID
>>> SELECT DISTINCT o_c_id
>>> FROM orders
>>> INNER JOIN order_line
>>> ON orders.o_id = order_line.ol_o_id
>>> and orders.o_id > @min_o_id
>>> AND order_line.ol_i_id = 5000;
>>>
>>>
>>> /*q3: for each line item position, add up how many units were listed
in
>>> each position (except position 5000) for all customers who had at
least
>>> 5000 line items in an order within the last 10000 orders. Limit the
>>> results to those line item positions with the top 50 qty values */
>>> SELECT SUM(ol.ol_qty) sumolqty
>>> , ol_i_id
>>> FROM orders o
>>> INNER JOIN order_line ol
>>> ON orders.o_id = order_line.ol_o_id
>>> AND orders.o_id > @min_o_id
>>> AND NOT (order_line.ol_i_id = 5000)
>>> INNER JOIN tmpOCID t
>>> ON orders.o_c_id = t.o_c_id
>>> GROUP BY ol_i_id
>>> ORDER BY sumolqty DESC
>>> limit 50;
>>>
>>> DROP TEMPORARY TABLE tmpOCID;
>>>
>>> If you are using a programming language to execute your statements,
>>> just
>>> make sure that you DO NOT close the connection between each
>>> statement and
>>> the whole thing should work as expected.
>>>
>>> I think you wanted to know was: the top 50 orders (by ranked by total
>>> units ordered) within the last 10000 orders that came from customers
>>> who
>>> had orders of at least 5000 line items (within the last 10000
orders).
>>> That changes "q3:" of the last batch to read:
>>>
>>> /* q3a: */
>>> SELECT o_id
>>> SUM(ol.ol_qty) sumolqty
>>> FROM orders o
>>> INNER JOIN order_line ol
>>> ON orders.o_id = order_line.ol_o_id
>>> AND orders.o_id > @min_o_id
>>> INNER JOIN tmpOCID t
>>> ON orders.o_c_id = t.o_c_id
>>> GROUP BY o_id
>>> ORDER BY sumolqty DESC
>>> limit 50;
>>>
>>>
>>> If I did not divine the purpose of your query correctly, just let me
>>> know.
>>> I am more than happy to try again.
>>>
>>> Shawn Green
>>> Database Administrator
>>> Unimin Corporation - Spruce Pine
>>>
>>
>> _________________________________________________________________
>> 与联机的朋友进行交流，请使用 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]
>>
>
> _________________________________________________________________
> 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn
>
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.336 / Virus Database: 267.9.2/54 - Release Date: 7/21/2005
--
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]
