王 旭 <[EMAIL PROTECTED]> 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
