From: "Becker, Holger" <[EMAIL PROTECTED]>
To: "? ?" <[EMAIL PROTECTED]>, <[email protected]>
Subject: RE: bad performance question
Date: Mon, 13 Jun 2005 10:16:50 +0200
[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]
thanks for your answer holger,but the statement be used in a
application.Only one parameter can be gave to the statement in the
application.I can't modify the Applicaiton.If i use your advice,i need five
parameter.Do you have any other means?
_________________________________________________________________
与联机的朋友进行交流，请使用 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]
