> There's always a tradeoff. You're probably noticing the cost of indexing everything in your commits, I cannot tell whether that is a particular index or the amount of indexes. What I'd recommend you to do, is to delete all indexes except the primary key, then start adding them one by one (taking the most important index first) and do some performance checking between each index (or between every few indexes). I'm sure each of these 30 indexes speed up a particular query, but I doubt you checked all other kind of queries (including UPDATE queries and commit) that may have become a bit slower due to the creation of each of these indexes.

yes their is a tradoff, and all the index are absolutely necessary. but for me it's completely unbelievable that the commit take soo much time and why the write to the disc is not done in a separate thread when force write is set to off (because the write need to be flush it's simply block the current query)! and worse, it's most of the time cause corruption of our database (we have a corruption of the database every 2 days now, it's a big disaster) ! i can not believe it ! it's like noone really use firebird in intensive application before me !

> In my ears, 30 indexes for 10 fields sounds crazy - I'm more used to up to about 10 indexes for 100 fields. Your situation is undoubtedly very different from mine, but I think a general rule should be to consider both benefits and drawbacks of a particular index before creating it.

if you have a solution to alway in every case gave me in < 100 ms a query with order by and filter on every combination of the 10 fields (actually it's was 15 and not 10) i m ready to take it ! even ready to pay the guy who will gave us the solution !

> As for 'ORDER BY', that is generally not the point of indexes. JOIN and WHERE would normally reduce the result set considerably and then ORDER BY could normally be done quicker without using any index at all.

in the dream world yes ! in the true world you will always have one query that will return you millions of rows and that must be also ordered

stephane