Re: Keeping the indexes in memory.

Albert Wisse wrote: > > Is it possible to keep the indexes in memory, to force it. When the > buffers/chache is full then first the table data must be flushed from > cache then the indexes. > > The reason for that is, that the database I use has an unique constraint > on every table and that means an index must be searched during an > insert, for a possible violation of the unique constraint. > With the index always in memory I hope to gain insert speed. > > Note: I have a lot of memory in my server so I can set the buffer count > very high in case I can't do anything else then increasing the number of > buffers. Since the buffer cache is set up as a set of LRU queues and within each queue pages are reused in Least Recently Used order, active index pages do indeed tend to stay in memory. So it looks like you are all set. The only thing that you could do that MIGHT be faster is IFF a large percent of inserts fail, you MAY speed processing by SELECTing the key before attempting an INSERT. If your logic is to UPDATE if the INSERT fails with a duplicate key then it will USUALLY be faster to attempt the UPDATE first and then INSERT if the UPDATE returns zero rows updated. This will be faster, depending on the number of indexes and the order in which they were created, even if many but not most INSERTS would fail. This is because of the cost of updating the data page and all of the indexes then having to roll it all back when the UNIQUE index is INSERTED to and fails. Art S. Kagel