| Author |
Topic |
|
fmardani
Constraint Violating Yak Guru
433 Posts |
 Posted - 2004-11-07 : 12:51:42
|
| There are several tables which are constantly being updated and inserted. At the same time these table are being used to retrieve data to the users in their search applications.I am thinking of placing indexes on these tables but then I am also thinking about the fact that they are constantly being used for inserts and updates. Any thoughts?Thanks |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-11-07 : 12:57:59
|
put an index on PK's and FK's. that way the searches will work and updates/inserts won't have much reindexing to do... i guess this would work best if you had int pk's and fk's.the only way to know is for you to try it.Go with the flow & have fun! Else fight the flow  |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-07 : 13:05:59
|
| Primary keys already have indexes. |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2004-11-07 : 13:36:02
|
| I guess you will agree that I put non-clustered indexes on FKs? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-07 : 13:39:03
|
| Yes. Any column that you regularly have in a WHERE or JOIN condition would be a good candidate for an index. At the same time, don't index every single column even if they all show up in a WHERE clause somewhere. |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2004-11-07 : 13:41:18
|
| The tables on the client site do not have Primary keys and no FK.How do you suggest I optimize their search queries. The problem is that I do not want to start creating PK columns and FKs on these tables as it will affect their application design. Hope you are following me.Thanks |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-11-07 : 13:53:58
|
| as rob said put them on the columns that are frequent in joins or where conditions.that does not need to be PK or FK... i suggested that because usually those are the connectiong columns.Go with the flow & have fun! Else fight the flow |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2004-11-07 : 14:01:03
|
| Yes as I thought. I will start placing non-clustered indexes on the commonly used fields in the where and inner joins.Thanks |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2004-11-07 : 14:06:55
|
| How about introducing covering indexes? |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2004-11-07 : 16:36:53
|
| The other question is have a look at this example in the northwind DB --> select city, customerid from customerswhy is the result ordered by city and not customerid? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-07 : 17:13:11
|
| There is no guarantee of order unless you use an ORDER BY clause, even with a clustered index. If you look at the execution plan of the query, the optimizer is doing an index scan on the City index. It does this because the City index is a covering index, it contains both the CustomerID and City values in the index alone. Add an additional column(s) to the SELECT list, or an ORDER BY CustomerID and it switches to a clustered index scan (PK_Customers). Never rely on any inherent ordering, always use ORDER BY if you need results in a particular order. |
|
|
|
|
index