Hello Liu, I only meant that - aside from simple cases - any suggestion must take the output of the EXPLAIN command into account (the complete output), so you should post it along with your query. If you only say 'the cost is 5443 and it's too high' there can no suggestion be given.
Regards Alexander Schröder SAP DB, SAP Labs Berlin > -----Original Message----- > From: 王 旭 [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 14, 2005 6:28 PM > To: Schroeder, Alexander; [email protected] > Subject: RE: optimize a sql statement > > I know the knowledge about you mean. > But can i optimize this operation at the status? > > >From: "Schroeder, Alexander" <[EMAIL PROTECTED]> > >To: 鐜?鏃?<[EMAIL PROTECTED]>, <[email protected]> > >Subject: RE: optimize a sql statement > >Date: Tue, 14 Jun 2005 16:16:06 +0200 > > > >Hello Liu, > > > >it would possibly help to identify whether there are any > possibilities to > optimize the statement if you post > >the EXPLAIN output, as the strategy chosen often depends on > the table > statistics, i.e. depends on the data > >contained in the tables. > > > >Regards > >Alexander Schr枚der > >SAP DB, SAP Labs Berlin > > > > > -----Original Message----- > > > From: 鐜?鏃?[mailto:[EMAIL PROTECTED] > > > Sent: Tuesday, June 14, 2005 3:56 PM > > > To: [email protected] > > > Subject: optimize a sql statement > > > > > > I have a sql statement been created basing three table. > > > This is the table's creating statement: > > > -------------------------------------------------------------- > > > ----------------------- > > > > > > CREATE TABLE "DBA"."ITEM" > > > ( > > > "I_ID" Integer NOT NULL, > > > "I_TITLE" Varchar (60) ASCII, > > > "I_A_ID" Integer, > > > "I_PUB_DATE" Date, > > > "I_PUBLISHER" Varchar (60) ASCII, > > > "I_SUBJECT" Varchar (60) ASCII, > > > "I_DESC" Varchar (500) ASCII, > > > "I_RELATED1" Integer, > > > "I_RELATED2" Integer, > > > "I_RELATED3" Integer, > > > "I_RELATED4" Integer, > > > "I_RELATED5" Integer, > > > "I_THUMBNAIL" Varchar (40) ASCII, > > > "I_IMAGE" Varchar (40) ASCII, > > > "I_SRP" Fixed (17,2), > > > "I_COST" Fixed (17,2), > > > "I_AVAIL" Date, > > > "I_STOCK" Integer, > > > "I_ISBN" Char (13) ASCII, > > > "I_PAGE" Integer, > > > "I_BACKING" Varchar (15) ASCII, > > > "I_DIMENSIONS" Varchar (25) ASCII, > > > PRIMARY KEY ("I_ID") > > > ) > > > > > > CREATE TABLE "DBA"."AUTHOR" > > > ( > > > "A_ID" Integer NOT NULL, > > > "A_FNAME" Varchar (20) ASCII, > > > "A_LNAME" Varchar (20) ASCII, > > > "A_MNAME" Varchar (20) ASCII, > > > "A_DOB" Date, > > > "A_BIO" Varchar (500) ASCII, > > > PRIMARY KEY ("A_ID") > > > ) > > > > > > CREATE TABLE "DBA"."ORDER_LINE" > > > ( > > > "OL_ID" Integer NOT NULL, > > > "OL_O_ID" Integer NOT NULL, > > > "OL_I_ID" Integer, > > > "OL_QTY" Integer, > > > "OL_DISCOUNT" Fixed (5,2), > > > "OL_COMMENTS" Varchar (100) ASCII, > > > PRIMARY KEY ("OL_ID", "OL_O_ID") > > > ) > > > CREATE INDEX "ORDER_LINE_OL_I_ID" ON > "DBA"."ORDER_LINE"("OL_I_ID" ASC) > > > CREATE INDEX "ITEM_I_A_ID" ON "DBA"."ITEM"("I_A_ID" ASC) > > > CREATE INDEX "ORDER_LINE_OL_O_ID" ON > "DBA"."ORDER_LINE"("OL_O_ID" ASC) > > > CREATE INDEX "ITEM_I_SUBJECT" ON "DBA"."ITEM"("I_SUBJECT" ASC) > > > -------------------------------------------------------------- > > > --------------- > > > > > > Follow is the sql statement: > > > -------------------------------------------------------------- > > > --------------- > > > > > > SELECT SUM(OL_QTY) SUMOLQTY,I_ID, I_TITLE, A_FNAME, A_LNAME > > > FROM DBA.ITEM, > > > DBA.AUTHOR, DBA.ORDER_LINE > > > WHERE ITEM.I_ID = ORDER_LINE.OL_I_ID AND ITEM.I_A_ID = > > > AUTHOR.A_ID AND > > > ORDER_LINE.OL_O_ID >1 AND ITEM.I_SUBJECT = 'COOKING' > > > GROUP BY I_ID, I_TITLE, A_FNAME, A_LNAME > > > -------------------------------------------------------------- > > > --------------- > > > > > > I using explain statement.Then i find the cost is 5443 though > > > the statement > > > wasn't using table scan search strategy.The cost is too high. > > > > > > Please help me optimize this sql statement. Thanks! > > > > > > _________________________________________________________________ > > > 涓庤仈鏈虹殑鏈嬪弸杩涜浜ゆ祦锛岃浣跨敤 MSN > > > Messenger: > http://messenger.msn.com/cn > > > > > > > > > -- > > > MaxDB Discussion Mailing List > > > For list archives: http://lists.mysql.com/maxdb > > > To unsubscribe: > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > _________________________________________________________________ > 享用世界上最大的电子邮件系统— MSN Hotmail。 > http://www.hotmail.com > >
