Subject Re: Index Design Question
Author mmenaz
--- In ib-support@y..., "andrew_s_vaz" <andrew_s_vaz@b...> wrote:
> Hi all,
>
> I have 2 tables in the db that are really busy. I figured out the
> ways that they are most readed and have some doubts on creating the
> indexes for them.

I don't know the answer, just give you some suggestions:
a) TEST, TEST, TEST!
in Firebird you don't have the problems you have with Paradox when adding indexes. Use a program like QuickDesk (it's commercial, but I think you can find some free that can do the job) that let's you run a query and has a "performance" report telling you index usage, number of fetches, and so on.
You can check PLAN, the way the optimizer uses indexes. Sometime it's fooled by the SQL query, so it needs some fine tune
Optimize your SQL syntax. Joining the wrong way can waste a huge amunt of time, and having some SQL "bad habit" involved can do the same thing (I've read some books pointing out how different syntax could make the server do few or huge amount of work for the same resoultset!)
Regards
Marco Menardi