January 12, 2004 at 4:59 am
In the database for a CMS there are tables where the primary key is a compound of 4 integer fields. Id, LanguageId, BrowserId, RevisionId.
The CMS executes a query that says
SELECT ID
FROM table
WHERE Id = 4567 AND LanguageId = 1 AND BrowserId = 1 AND State=2
This brings back all live revisions of an object for a particular language and browser combination.
The query bring back 11 rows. If I omit the State=2 then 14 rows are returned.
The execution plan says that a Clustered Index Seek is carried out, however the execution plan doesn't change when the State=2 condition is added or removed.
January 13, 2004 at 8:38 am
Is the State field the trailing edge of the index. If it is then that is the behavior that you should expect. Since it is using the front edge of the key to limit the rows it is 'seeking' through. Take off the front edge of the index, which I am assuming is the Id field, and you should see a clustered index scan, which is the same as a table scan.
Hope this helps.
Tom
January 13, 2004 at 8:55 am
Interesting stuff!
The State field is the next field following the 4 fields that comprise the clustered primary key if that is what you mean by being on the trailing edge.
I was expecting more in the execution plan than a single clustered index seek.
January 13, 2004 at 9:26 am
I did not fully process your original post, what I meant by trailing edge was the RevisionId field.
There's not much else but a clustered index seek that this query could do. Try putting a distinct in there and you'll see a sort step show up.
Thanks
Tom
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply