jasperelzinga wrote:

>
> We have a multi-user Customer Relation Management (CRM) program with
> Firebird as database back-end. To speed things up, we are now planning
> to place indices on the fields where the users have their tables
> sorted on. This brings some new problems with it, and I was wondering
> if anyone here has some clever ideas about it.

Yes. Don't do it. Use indexes to select data, not to order it. In
other words, create indexes on fields that are used in ON and WHERE
clauses.

>
> For example.. the program is multi-user, so what should we do if 10
> different users use 10 different columns to sort their table on? Just
> create an index on every column? Would that slow down the SELECT
> queries a lot?

Using an index to return rows in sorted order is somewhat slower
than reading the data in natural order and sorting it it memory.
Sorts are fast. Reading in an order other than storage order is
slow. The exception is when you want to see only the first few
records in sorted order. In that case, reading all the records
and sorting them is a waste of time. If, however, you do expect
to process all the records, you'll get to the last records sooner
with a sort.

So, using an index to avoid a sort generally makes select with
an order by clause slower. Having lots of indexes to support
many different selects will make inserts, updates, and deletes
much slower.

>
> And another example.. what if a user start the program, chooses a
> certain column to sort on. Then the program crashes, and he has to
> restart/reinstall it again. But now he chooses a different column to
> sort on, meaning the other index will still be there.

Don't create indexes dynamically to eliminate a sort.



Regards,


Ann