select id from msEmployee where createdby='me' or createdbyemployeeid=2
index can't be used with or statement, so how do we modify the query so it will be using the index? There is one way to make use index for or statement. We need to separate the or query into its own syntax and join it together using union or union all (faster). So for query above we separate those two or into this query.
select id from msEmployee where CreatedBy='me' union select if from msEmployee where createdbyemployeeid=2
to measure your query performance you can use syntax set statistics on (before execute) and set statistics off (after execute)
this is the result comparing between union, union all or just or statement (notice that union and union all doesn't seem to be that different however union all will perform faster because it won't force distinct the result)
this was the index definition for that table
ref:
- https://stackoverflow.com/questions/751838/create-an-index-on-sql-view-with-union-operators-will-it-really-improve-perform
- https://social.msdn.microsoft.com/Forums/sqlserver/en-US/37adc0b7-e8f3-4fd4-bb0f-8e65d9e13a94/how-to-measure-the-performance-of-query-has-improved-after-index?forum=sqldatabaseengine
