hello schroeder,
I notice a phenomenon.
When the condition "ITEM.I_SUBJECT = 'COOKING'" be changed into
others,the costvalue 5443 turn to 19!!
For example, "ITEM.I_SUBJECT = 'qwerqwer'" or "ITEM.I_SUBJECT = 'COOK'"
or "ITEM.I_SUBJECT = 'COOKINGG'".
why??
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 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]
