How many indexes is too many?

Reading some postings, they mention not to use to many indexes (gets buggy).
One of our VFP6 tables has 85 fields and ~ 120,000 records and growing. We do
a variety of queries on many fields. Currently we have 8 indexes and would
like to add a few more. I was wondering what a safe # of indexes is without
compromising anything.? Any input/experiences is appreciated.

TIA
Mike

Paul
Fri Nov 26 16:54:43 CST 2004

So far, I haven't run into problems involving too many index tags. You
should be aware though that maintaining indexes does involve some overhead,
so don't create more than you actually use. I suspect that adding indexes
willy-nilly will slow down table updates.



"Michael" <Michael@discussions.microsoft.com> wrote in message
news:BD85ADE5-8B4C-4F7D-A102-6AD161B17F24@microsoft.com...
> Reading some postings, they mention not to use to many indexes (gets
> buggy).
> One of our VFP6 tables has 85 fields and ~ 120,000 records and growing. We
> do
> a variety of queries on many fields. Currently we have 8 indexes and would
> like to add a few more. I was wondering what a safe # of indexes is
> without
> compromising anything.? Any input/experiences is appreciated.
>
> TIA
> Mike

Dan
Sat Nov 27 00:23:01 CST 2004

"gets buggy" is an inadequate description of a technical problem, real or
perceived, and I'd never base a software engineering decision on such a
vague description.

120K records is actually pretty trivial for a Fox app. (At least as far back
as Foxbase+ in DOS.) 8 Index tags or more shouldn't be a problem.

You MAY experience performance problems if the table has high volume
inserts/updates. Every insert or update (no matter what form they may take)
requires an update to each index, and that means writing to disk. Writing to
disk is the slowest thing you can do.

However, that performance hit may be offset by query speed benefits from
having the index elsewhere in the app. The way to see if either is an issue
for you is to try it and see. There's no rulebook here.

Dan

Michael wrote:
> Reading some postings, they mention not to use to many indexes (gets
> buggy). One of our VFP6 tables has 85 fields and ~ 120,000 records
> and growing. We do a variety of queries on many fields. Currently we
> have 8 indexes and would like to add a few more. I was wondering what
> a safe # of indexes is without compromising anything.? Any
> input/experiences is appreciated.
>
> TIA
> Mike

Andrew
Mon Nov 29 07:44:08 CST 2004

Michael wrote:
> Reading some postings, they mention not to use to many indexes (gets
> buggy). One of our VFP6 tables has 85 fields and ~ 120,000 records
> and growing. We do a variety of queries on many fields. Currently we
> have 8 indexes and would like to add a few more. I was wondering what
> a safe # of indexes is without compromising anything.? Any
> input/experiences is appreciated.


We use FPW2.6, there is a table with 17 indexes, 40 fields, 200000 records
and I have twice seen the following problem in the past 5 years:
http://support.microsoft.com/default.aspx?scid=kb;en-us;125576

As you will see from the article, that particular problem is only a known
issue with FPW2.6a and VFP3 but the only thing I could really put it down to
was "lots of indexes / complex index expressions."

[incidentally, both times I just quit all the workstations with the table
open and then it all started working again.]

Maybe there are other issues related to lots of indexes, I'm not even
certain that was the cause of the problem we experienced but I think you
should be fine to add a bunch more indexes to your table.

--
HTH
Andrew Howell

Craig
Mon Nov 29 14:24:10 CST 2004

From VFP Help: "The number of tags in a compound index file is limited only
by available memory and disk space. "

--
Craig Berntson
MCSD, Visual FoxPro MVP
www.craigberntson.com
Salt Lake City Fox User Group
www.slcfox.org
www.foxcentral.net


"Michael" <Michael@discussions.microsoft.com> wrote in message
news:BD85ADE5-8B4C-4F7D-A102-6AD161B17F24@microsoft.com...
> Reading some postings, they mention not to use to many indexes (gets
> buggy).
> One of our VFP6 tables has 85 fields and ~ 120,000 records and growing. We
> do
> a variety of queries on many fields. Currently we have 8 indexes and would
> like to add a few more. I was wondering what a safe # of indexes is
> without
> compromising anything.? Any input/experiences is appreciated.
>
> TIA
> Mike

Ook
Mon Nov 29 14:57:39 CST 2004

10 or 12 indexes on a table is not unreasonable. I've seen a lot more then
that. I've never seen any problems caused with having too many indexes, and
I do not believe you will compromise anything by adding more. I've worked
with 1.8GB tables with about a dozen indexes, and the only problem we ever
had was that it took over an hour to reindex the table. Also, you probalby
do not want an index on deleted() if the table will get very large, as this
can cause performance problems. Go for it, add what you need, and don't
worry about it.

> "Michael" <Michael@discussions.microsoft.com> wrote in message
> news:BD85ADE5-8B4C-4F7D-A102-6AD161B17F24@microsoft.com...
> Reading some postings, they mention not to use to many indexes (gets
> buggy).
> One of our VFP6 tables has 85 fields and ~ 120,000 records and growing. We
> do
> a variety of queries on many fields. Currently we have 8 indexes and would
> like to add a few more. I was wondering what a safe # of indexes is
> without
> compromising anything.? Any input/experiences is appreciated.
>
> TIA
> Mike