Depending on the amount of RAM your server has, it might just read the whole table into memory and keep it there - so your first query will load it, and any subsequent queries will just operate on those pages loaded in memory.
To get any meaningful data, you need to flush the cache before every run of your query! (run DBCC FREEPROCCACHE
and DBCC DROPCLEANBUFFERS
before any query execution!)
I tried your scenario with 100'000 dummy file names and almost the same table structure (I changed FileName
to be VARCHAR(260)
).
Without index:
Table 'TestFiles'. Scan count 1, logical reads 2137, physical reads 5, read-ahead reads 2136, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Query Subtree Cost 1.68884
With non-clustered index on filename:
Table 'TestFiles'. Scan count 1, logical reads 6, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Query Subtree Cost 0.0065704
This was done with this statement:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
SELECT *
FROM testfiles
WHERE FileName = 'File-D8584B44-518F-428A-86A1-7836E0B60502'
No, you can specify the 'params' (the parts of the where
clause) in any order and the query optimizer will handle it. The optimizer will do the filtering in the order that it estimates is most efficient, but note that this is more complex than just choosing which order to filter: filtering might be done before or after joining for example.
You can't exactly prove this, but you can demonstrate it is true for a particular query by experimenting and seeing if the plan changes. It may even be true that there are edge cases where the order does matter, but my advice would be to ignore the possibility and assume it never happens as otherwise you will expend a lot of effort trying different permutations. Much better to focus on the kind of tuning which you know can pay dividends (eg correct indexing).
Best Answer
By default
PRIMARY KEY
is already a clustered indexed. It will also speed up queries executed against first column in an index, but not the other.So if you index is defined on
user_id
,role_id
(in that order), index will work also foruser_id
.In your situation I'd go with default index of compound key, then, if needed, take a closer look at long running queries and benchmark if separate index on
role_id
is necessary.