List:General Discussion« Previous MessageNext Message »
From:SGreen Date:July 19 2005 7:21pm
Subject:Re: optimize a order by statement
View as plain text  
王 旭 <liulian0588@stripped> wrote on 07/18/2005 03:17:51 AM:

> Follow is the sql statement:
> 
> --------------------------------------------------------
> explain SELECT ol_i_id
> FROM orders, order_line
> WHERE orders.o_id = order_line.ol_o_id and o_id>0
> ORDER BY SUM(ol_qty)DESC
> --------------------------------------------------------
> 
> Follow is the explain output:
> 
> --------------------------------------------------------
> 1, 'SIMPLE', 'orders', 'range', 'PRIMARY', 'PRIMARY', '4', '', 129615, 
> 'Using where; Using index; Using temporary'
> 
> 1, 'SIMPLE', 'order_line', 'ref', 'PRIMARY,order_line_ol_o_id', 
> 'order_line_ol_o_id', '4', 'tpcw.orders.o_id', 1, ''
> --------------------------------------------------------
> The query plan use "Using temporary".Can i optimize this sql statement?
> 


Yes I think there is a way to improve the performance of your query. 
Without a GROUP BY clause, the function SUM() results in the same value 
that was originally in the field. You may be able to make this query much 
faster if you eliminate the useless function call and rewrite your query 
as:

SELECT ol_i_id
FROM orders, order_line
WHERE orders.o_id = order_line.ol_o_id and o_id>0
ORDER BY ol_qty DESC;

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
optimize a order by statement王 旭18 Jul
  • Re: optimize a order by statementSGreen19 Jul