Now i create a table.
Follow is the create 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")
)
After creating the table,I do a query.
Follow is the query statement in a applation:
SELECT J.i_id,J.i_thumbnail from item I, item J where (I.i_related1 =
J.i_id or I.i_related2 = J.i_id or I.i_related3 = J.i_id or I.i_related4 =
J.i_id or I.i_related5 = J.i_id) and I.i_id = ?
The "?" is a parameter.I find there are happen a huge performance quetion.
I make a query plan for the query statement with "explain".I find there are
"table scan" for table "J" and the cost is very large.
I feel "OR" is the reason.Index maybe be invalidated when using "OR" key
word.
Can you tell me how to dealing when a statement using "OR" and how to
optimize the operation?
There is a notice that the "I.i_id = ?" only be used once.
_________________________________________________________________
享用世界上最大的电子邮件系统― 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]
