Performance based on design

I was reading an earlier post and came across the following message:

In any case, discussing the size of a database or the hardware it runs
on usually isn't as important as how well it has been designed. If you
have a well designed database which is properly indexed and accessed
using well-written code, then it will perform and scale well up to
very large amounts of data. If you don't, then you can have
performance problems with even small amounts of data.


Which brought up a few questions, and perhaps there isn't a good answer but what are some "quidelines" when someone builds a database? Such as what would reason I would want to index a field or table? (btw..I do know what indexing is) But there are pros and cons?

Thanks

re: Performance based on design

Difficult to come up with absolutes here because each database system is different and you need to weigh up the pro's and cons accordingly.

1) Indexes speed up table scans because they reduce the number of rows that a query has to search through.
Indexes slow down table inserts and deletes because every time rows are added or deleted the indexes also needs to be updated.

So the more often inserts and deletes occur on a table the less you want to impede that with indexes.

2) Query speed is relative to table size.
So the larger a table becomes the more you will want to speed queries up with indexes.

3)Tables can have more that 1 index.
So the more variation in query slection criteria there is the more you will want to increase the number of indexes to suit.

4) With point 1 in mind how can we balance 2 and 3 for best overall performance

Is that the sort of thing you have in mind?

re: Performance based on design

1) Indexes speed up table scans because they reduce the number of rows that a query has to search through.
Indexes slow down table inserts and deletes because every time rows are added or deleted the indexes also needs to be updated.

So the more often inserts and deletes occur on a table the less you want to impede that with indexes.

2) Query speed is relative to table size.
So the larger a table becomes the more you will want to speed queries up with indexes.

3)Tables can have more that 1 index.
So the more variation in query slection criteria there is the more you will want to increase the number of indexes to suit.

4) With point 1 in mind how can we balance 2 and 3 for best overall performance

Is that the sort of thing you have in mind?[/quote]

Yes it was. But what would be the cutoff point when the index is actually slowing down the system becuase of inserts?

I have a home build Classic ASP (yes I know CASAP is also slower) user directory that searches the home address, phone, major, and degree. Overall, there is 8 tables that are linked together for this search, howerver none of them are indexed. The largest tables is around 200K rows. If I wanted to try to increase the speed with indexing, what columns would I index.. the ones it searches by or the ones it displays?

Thanks

re: Performance based on design

Hands down, the one you always searched for.

-- CK

re: Performance based on design

Yes,what ck said. Indexes don't speed displaying data, only searching data.
So if you find many queries are seaching a table and retrieving records between a date range, then the date field would be an obvious candidate for an index.

re: Performance based on design

I just re-read that and I didn't mean to imply that dates are the only thing that can be indexed