Printable View
I've got a table like this
SalesOrderId(PK)
OrderDate
SalesPersonId (FK1, l1)
StoreID (FK3, l3)
TerritoryID (FK2, l2)
I want to apply an index to make my query faster/more effecient.
The query is pulling out the SalesOrderId,OrderDate and StoreID WHERE StoreID=X
Would it be best to apply an index to StoreID, would it improve performance?
Cheers
It should make that query faster... but it will slow down any insert/update, and may slow down queries that don't use the same field.
If that kind of query forms the majority of work your database will do with that table, it would be a good move to add the index.. if not it is hard to tell, so try it and see.
Is this MS SQL Server?
Are there going to be truly many, many rows in this table??
yup, 10,000 rows expected. Haven't decided on a RDBMS yet, just planning. Cheers all.
10,000 rows is not a lot.
If it was a huge figure I was going suggest clustered primary index - which would require a different table layout.
Also - MS SQL has a little known benefit of the fact that if you put extra column in the index - those that you are "actually" selecting on - then the index is used for not only finding the rows (the WHERE part) part but also supplying the data (the SELECT list part).
But again - these concepts really only become important when you are talking 100's of thousands of rows.
Sounds good, I haven't come across stuff as advanced as that.Quote:
Originally Posted by szlamany
Thanks all, rated.
If the majority of your queries include Where StoreId = ?, then the StoreId field is a good candidate for a Clustered Index.