Index, Questions and assumptions.

[MikeData]

Index (critical in a DB), Questions and assumptions.
Must of us come from other program where certain rules apply.
I would like to confirm those rules here in Alpha, as I would like to improve the performance of the DB.
The manual mentions all about indexing, but not what are the best options.
I am referring to tables of 100,000+ records.

1. The rec() or first ID must always be indexed Yes of course.
2. A field needed for search under �Search by key� Yes of course.
3. Which is faster, Numeric or Character Index?

4. For every field involve in a Lookup, DbSum and TableSum code, those fields must be indexed?
5. Fields involved in a set structure, Do they have to be indexed?
6. If not, what is the best scenario?

7. Why is it, that after a few codes are added to the tables and forms, Alpha makes the same field indexed multiple times?
8. Is it needed or can it be redesigned?

9. If a key field (which is indexed) is composed of 3 other fields, Do those other 3 fields, has to be indexed, besides that key field?

10. Can index be built for one time use, as needed? Like in reports or queries.

I believe the most practical, is to have the less amount of indexes.
11. On the other hand is there a limit in functionality of the amount of indexes?

12. Is Lightning Query Optimization feature used automatically (as long the fields follow the rules)?

A summary clarification will be most appreciated.
I am sure will help myself and most new comers.

[Stan Mathews]

Re: Index, Questions and assumptions.

No one else has responded so here's my view, based on personal experience.

1. The rec() or first ID must always be indexed Yes of course.
Inherent to the dbf definition.
2. A field needed for search under �Search by key� Yes of course.
If you want to search by key on a field there must be an index key on that field, again, by definition.
3. Which is faster, Numeric or Character Index?
No difference.
4. For every field involve in a Lookup, DbSum and TableSum code, those fields must be indexed?
No, LookupN(), LookupC(), etc and any of the db functions, dbsum(), dbmin(), etc require indexes, table functions tablesum(), etc and plain lookups() do not - that's why the first group is faster.
5. Fields involved in a set structure, Do they have to be indexed?
Linking fields, yes, others no.
6. If not, what is the best scenario?

7. Why is it, that after a few codes are added to the tables and forms, Alpha makes the same field indexed multiple times?
Alpha doesn't. Alpha creates indexes as needed to satisfy set linkages.
8. Is it needed or can it be redesigned?

9. If a key field (which is indexed) is composed of 3 other fields, Do those other 3 fields, has to be indexed, besides that key field?
No.
10. Can index be built for one time use, as needed? Like in reports or queries.
You can use xbasic to add, modify, and remove indexes. If the index will be used more than once it is generally advisable to save it. The time required to build an index is roughly the same as the time required to run a query.
I believe the most practical, is to have the less amount of indexes.
Indexes must be maintained. If you have batch operations or scripts that append or update a table, each index must be updated each time the operation is run. this will slow down the operation.
11. On the other hand is there a limit in functionality of the amount of indexes?

12. Is Lightning Query Optimization feature used automatically (as long the fields follow the rules)?
As long as you follow the LQO rules LQO will be used automatically.

[Ted Giles]

Re: Index, Questions and assumptions.

If you men in 9 that the Primary Key is based on an expression then only the expression needs to be the source of the index. It will be slower I suspect as if you update the Zip, the whole idx is updated, whereas just updating a singlr field should be as fast as it will get.

Index Name1 = "name" <- field
Index Name2 = "name"+"Address_line1"+"Zip_Code" <-expression

Wait until you see the response when you index EVERYTHING! I presume the feature is still available but I only tried it once:D

Hope this helps.

[Ted Giles]

Re: Index, Questions and assumptions.

If you men in 9 that the Primary Key is based on an expression then only the expression needs to be the source of the index. It will be slower I suspect as if you update the Zip, the whole idx is updated, whereas just updating a single field should be as fast as it will get.

Index Name1 = "name" <- field
Index Name2 = "name"+"Address_line1"+"Zip_Code" <-expression

Wait until you see the response when you index EVERYTHING! I presume the feature is still available but I only tried it once:D

Hope this helps.

[Michael Humby]

Re: Index, Questions and assumptions.

Stan,

Could you please expand your experiences on this subject because I have never found the need when appending or updating.

If you have batch operations or scripts that append or update a table, each index must be updated each time the operation is run.

Michael

[Stan Mathews]

Re: Index, Questions and assumptions.

I meant Alpha must update (keep in sync) each index on a table when the update or append finishes. Sorry to be obtuse.

[Michael Humby]

Re: Index, Questions and assumptions.

Stan,

Thanks, I was beginning to get worried.

Michael

[MikeData]

Re: Index, Questions and assumptions.

Thanks Stan and Ted for the confirmations.
I am trying to maximized the speed of this DB, so I am compiling as many tips that could be implemented.
If any one has more tips, please mention them.

Thanks again.