Follows are the explain output:
---------------------------------------------------------------------------------------
ONWE TABLENAME COLUMN_OR_INDEX STRATEGY PAGECOUNT
DBA ITEM ITEM_I_SUBJECT EQUAL CONDITION FOR INDEX
810
DBA AUTHOR A_ID JOIN VIA KEY COLUMN 125
TABLE HASHED
DBA ORDER_LINE ORDER_LINE_OL_I_ID JOIN VIA INDEXED COLUMN 20532
RESULT IS COPIED , COSTVALUE IS 5443
----------------------------------------------------------------------------------------
From: "Schroeder, Alexander" <[EMAIL PROTECTED]>
To: ????<[EMAIL PROTECTED]>, <[email protected]>
Subject: RE: optimize a sql statement
Date: Wed, 15 Jun 2005 08:18:34 +0200
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??er
> >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
>
>
_________________________________________________________________
享用世界上最大的电子邮件系统― MSN Hotmail。 http://www.hotmail.com
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
