I�m pretty confused, changing just part of the query made a big difference
in performance:
This query executes very fast...
SELECT p.Property_ID, p.Address, p.Asking_Price, p.Baths, p.Beds, c.Name
City, p.Description_Client,
p.Is_Featured, p.Featured_Headline, p.Date_Listed, p.State,
p.Zip, p.Photo, p.MLS_ID,
p.Area_ID, p.Property_Status_ID, p.Virtual_Tour,
p.Number_Of_Units,
p.Number_Of_Buildings, p.Acres, p.Sold_Price
FROM Property p INNER JOIN City c ON p.City_ID = c.City_ID
WHERE (p.Exp_Date >= TIMESTAMP OR adddate(p.MLS_Timestamp, 125) >=
timestamp)
AND p.Area_ID = 1
AND p.MLS_ID in ('20241819')
EXPLAIN OUTPUT
P IDXPROPERTYMLS EQUAL CONDITION FOR INDEX 3916
MLS_ID (USED INDEX COLUMN)
AREA_ID (USED INDEX COLUMN)
C CITY_ID JOIN VIA KEY COLUMN 7
TEST RESULT IS COPIED , COSTVALUE IS 4
While this query takes over 30 seconds to execute:
SELECT p.Property_ID, p.Address, p.Asking_Price, p.Baths, p.Beds, c.Name
City, p.Description_Client,
p.Is_Featured, p.Featured_Headline, p.Date_Listed, p.State,
p.Zip, p.Photo, p.MLS_ID,
p.Area_ID, p.Property_Status_ID, p.Virtual_Tour,
p.Number_Of_Units,
p.Number_Of_Buildings, p.Acres, p.Sold_Price
FROM Property p INNER JOIN City c ON p.City_ID = c.City_ID
WHERE (p.Exp_Date >= TIMESTAMP OR p.MLS_Timestamp >= subdate(timestamp,125))
AND p.Area_ID = 1
AND p.MLS_ID in ('20241819')
EXPLAIN OUTPUT
P IDXPROPERTYMLS EQUAL CONDITION FOR INDEX 3916
MLS_ID (USED INDEX COLUMN)
AREA_ID (USED INDEX COLUMN)
C CITY_ID JOIN VIA KEY COLUMN 7
TEST RESULT IS COPIED , COSTVALUE IS 4
The only difference between the queries is:
p.MLS_Timestamp >= subdate(timestamp,125) is replaced by
adddate(p.MLS_Timestamp, 125) >= timestamp
Thanks,
John
-----Original Message-----
From: John O. Hampton, Jr. [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 07, 2003 6:16 PM
To: '[EMAIL PROTECTED]'
Subject: Query Performance Question
Hi,
�
Does the order of the conditions in a where clause make a difference?� I
have a query that includes the columns that map to a unique index.� If I put
the conditions containing these columns first in the where clause the query
executes very quickly.� If I put other conditions first then the query takes
over 30 seconds to run.� Is this expected or is this a bug?� If this is
expected where could I have found this in the documentation?
�
Thanks,
�
John
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
