October 12, 2005 at 8:59 am
This query, and many similar queries, appear quite frequently in an application I am trying to speed up:
SELECT accountID
FROM accountsTable
WHERE archived = 0
AND accountType = 1
AND (
createdByUserID = 1000251
OR modifiedByUserID = 1000251
)
I have a clustered index on createdByUserID, but the OR causes an index scan. Is there some way I can rewrite this, or a better way to index it to avoid the index scan?
For the sake of simplicity, we can assume the table only has the above fields, and archived and accountType are both bit fields with fairly even distribution.
Thanks,
--Glenn
October 13, 2005 at 12:49 am
Hi,
Is it a mistake in the posted SQL, or it REALLY queries the modifiedByUserID column ? Try first putting an index on it, too.
Regards,
Goce.
October 13, 2005 at 7:01 am
Try this:
SELECT accountID
FROM accountsTable
WHERE archived = 0
AND accountType = 1
AND createdByUserID = 1000251
UNION
SELECT accountID
FROM accountsTable
WHERE archived = 0
AND accountType = 1
AND modifiedByUserID = 1000251
)
October 13, 2005 at 10:46 am
Using union instead of or is good idea. On top of that index on modifiedByUserID would help peformance.
Clustered index on createdUserID causes a lot of page splits during inserts. Consider clustering primary key, I guess it is AccountID which is identity column. If the where condition mentioned is typical for most queries you can make two non-clustered indexes - first on createdByUserID, archived and accountType and second on modifiedByUserID, archived and accountType and clustered index on accountID as I already mentioned.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply