[EMAIL PROTECTED] wrote:
> 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.
Hi,
you are right the "OR" is the reason.
Because of the Ors the optimize couldn't use any
better strategy than table scan.
I would suggest do use an union.
You could try it with
SELECT J.i_id,J.i_thumbnail
from item I, item J
where I.i_related1 = J.i_id and I.i_id = ?
union all
SELECT J.i_id,J.i_thumbnail
from item I, item J
where I.i_related2 = J.i_id and I.i_id = ?
union all
SELECT J.i_id,J.i_thumbnail
from item I, item J
where I.i_related3 = J.i_id and I.i_id = ?
union all
SELECT J.i_id,J.i_thumbnail
from item I, item J
where I.i_related4 = J.i_id and I.i_id = ?
union all
SELECT J.i_id,J.i_thumbnail
from item I, item J
where I.i_related5 = J.i_id and I.i_id = ?
Kind regards
Holger
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
