Subject Thoughts about muti-user and indices
Author jasperelzinga

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.

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?

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. This is asking
for some kind of control-function that checks the indices every now
and then. But when should this check happen? Every time the program
starts up.. or every time someone changes his sort-column.. or should
it be a procedure in the database that takes place every hour/day/week?

We have been thinking a lot about solutions for this problem, but we
get the feeling that we are "reinventing the wheel". There must be
more people who want the same thing as we want. Does anyone know a
website or a book where we can find more information about this topic?

All idea's and sugestions are welcome!

Thanks in advance,

Jasper Elzinga