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]
