Subject [firebird-support] Re: commit after insert is very slow
Author Svein Erling Tysvær
>> Are you absolutely sure that you really need all these indexes?

>sure, and absolutely sure ! because it's a critical that select query return in < 100
>ms, and for that we need all theses index :(

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.

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.

>i forget to say that it's meanly because of different "order by" choice that i need all >thoses index ...

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.