Are indexes evil?

[tlyon]

I've been working on making an application more stable, and with the help of Finian Lennon's suggestions on database maintenance, have gained some ground. Recently, after a respite of about two weeks from serious index corruption problems, my application went ballistic. I was rebuilding the indexes 2 or 3 times per day. Finally, in exasperation, I decided to try various fixes to lessen the effects of the problem.

Having been quite a believer in indexes for some time, I decided to abandon indexes in favor of queries. It turns out that in this instance, queries seem to be more stable.

There is a downside, but it is workable. The server machine maintains indexes whereas the workstation must maintain the queries. Processor usage increase is noticeable. Still, even with that trade-off, it is worth tolerating the processor increase for better application stability.

One other interesting factoid I stumbled onto involves refreshing an embedded browse. It appears that

     tbl = table.current()
     tbl.index_primary_put("SomeIndexName")
     tbl.fetch_goto(1)
     :SomeFormName:browse1.resynch()

does not give the same results as:

     :SomeFormName:browse1.index_set("SomeIndexName")
     :SomeFormName:browse1.resynch()
     :SomeFormName:browse1.fetch_goto(1)

I'm curious why there is a difference.
If anyone has some free time to expound, I would enjoy hearing your thoughts.

Tom Lyon

[William Perry]

RE: Are indexes evil?

Tom: The more expereienced respondents to this board will probably have a better idea, but if the SomeFormName form is based on a set, the first piece of code will take you to the first record in the parent table and the second would leave you on the parent table record where you were to begin with - I think. -Bill

[William Perry]

RE: Are indexes evil?

p.s. - I don't think indexes are evil - but I think you have to use them very sparingly, especially when you get a lot of users (i.e. simple expressions and as few indexes as possible). -Bill

[csda1]

RE: Are indexes evil?

William,

Absolutely not. Within appropriateness to the project, more indexes are better. And more complicated expressions chosen judiciously will minimize duplicative indexes and speed an application.

The indexes pay the main penalty at record save time (also at reindexing and packing time), queries pay the penalty every single time they are run. If the application requires efficient operation and speed, you must use indexes. I have many indexes for my applications and do not experience correuptions. This implies there are differences in either how I use indexes from what Tom is doing or there are bad client workstations or network cards/cable/hubs on Tom's network corrupting the indexes. While Alpha 5 may have some bugs in some areas (e.g. using index filters cause some problems), they are generally very reliable.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[William Perry]

RE: Are indexes evil?

Ira: I did not mean to say that queries are a good substitute for indexes in situations where the query will need to be run frequently. Nor do I mean to imply that A5 has bugs that corrupt indexes. But the various things that you cite (hub/router problems, network cards, as well as problematic index expressions and/or application problems) can all cause index corruption, "can not add key to tag" errors, etc. Judicious use of indexes simply minimizes the opportunity for these problems. And like table and set structure, simplicity is the goal (i.e. connect to the customer table on cust_id instead of first_name + last_name + address_1 + zip_code, etc.) Maybe what I have made my point unclear by stating something that is too obvious. -Bill

[tlyon]

RE: Are indexes evil?

We might have some of those hardware issues here. All I can say as of now is that since abandoning indexes in favor of queries, things are running much more stable. I want to do some experimentation to find out why one or the other might have its advantages. It appears to only be a factor with one particular application I have created. Unfortunately, that is a very important application in our daily operations.

Tom Lyon