January 21, 2004 at 3:08 am
Hi,
I'm in the process of trying to optomise some search queries and was looking for some help in which indexes to create.
If I've got a table with the following columns;
datasource_id, model_id, from_date, to_date, a, b, c, d, e
If I'm searching in the a, b, c, d or e fields for some data and trying to return the model_id, if I know the datasource_id, a date between which the from_date and to_date should lie, then should I create a separate indexex for each of the a,b,c,d,e fields (together with the datasource_id, from_Date and to_date) or can I create one single index that covers every field ? Is that poor form ?
And then if I'm going to join this search table to another one by the model_id, should that be created as a separate index ?
Thanks in advance !
cheers
Andy
January 21, 2004 at 6:19 am
IMO, create FKsupporting-indexes for all FK-columns or columngroups (exact column-order as PK).
If you want more usable replies, post the DDL and expected stats.
<<searching in the a, b, c, d or e fields>>
if that is a or b or c or d or e, separate indexes would be preferable. Depends on #rows and cardinality and the datatype. If you know that combinations are always being used, you could provide a combined index.
Write the queries you had in mind. Avoid coding for unknown values or use of "like '%'+@var+'%'" (nonsargeble). It may take some more development-time, but your queries may be performing better if you split them up.
Test-Test-Test
Search http://www.sqlservercentral.com for "index"
these articles might get you on track
http://www.sqlservercentral.com/columnists/rgummadi/indexingstrategies.asp
http://www.sqlservercentral.com/columnists/chedgate/clusterthatindex.asp
http://www.sqlservercentral.com/columnists/sjones/wheredoiwanttogotodayindextuningwizard.asp
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 21, 2004 at 7:03 am
Hi,
Thanks for the response. I've grabbed the articles and will go through them. One useful situation is that all of the data is read-only so it can take however long it likes to create indexes as it won't be doing it on live data. Once all the indexes & data is uploaded then the db will be switched over from the upload to the live & vice-versa.
Cheers
Andy
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply