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] > >
