[thelist] database indexes (was:(no subject))

[martin burns]

Rudy wrote:
> > How does the ratio of indexes to information impact
> > dB performance? How can I justify not moving 7 or 8 tables
> > with 3-5 fields each into the main table (giving it an additional
> > 15-30 fields) other than on the fact that we have 20 odd hourse
> > invested in code already?
> 
> ah, well, now it's getting trickier
> 
> first of all, indexes *improve* performance -- except for inserts,
> updates,
> and deletes, where they slow it down
> 
> it's a balance, and it's not always easy to find
Phil Greenspun has more at
http://www.arsdigita.com/books/panda/databases-choosing
and describes it thus:
"That is the joy and the agony of SQL. Inserting two innocuous looking words [such as 'primary key'] can cost you a factor of 1000 in performance. Then inserting a sentence (to create the index) can bring you back so that it is only a factor of two or three."

Cheers
Martin