Primary Key and Indexing

[gear]

Could anyone tell me about the pros and cons of Primary Key and Indexing?

 

[The_Doc_Man]

Could anyone tell me about the pros and cons of Primary Key and Indexing?

It's not like there is always much of a choice. If you have a primary key, you will have the index of the primary key. No question. End of story. So the only issues you might discuss would be when do you NEED these entities? And when do you NOT?

If you want to define a one//many relationship, the "one" side of the relationship MUST have a primary key (and therefore a primary index). Again, no choice, no discussion.

If you are going to implement a lookup, some form controls require a primary key. Again, no choice, no discussion.

If you are going to search the table a lot even via direct SQL, having an index on a field (primary or otherwise) CAN make the search faster by a lot. There are times when it makes a LOT of difference because a precompiled query that can see indexes will optimize the query internally.

The down side of having too many indexes (you can have up to 10 on a table) is that if the table experiences lots of DELETES or APPENDS or UPDATES (in the latter case, updating the indexed field), there is a lot of overhead because the index has to be modified. For huge tables, the indexes must be completely rebuilt. Which is why some folks doing massive (wholesale) data changes on tables with lots of indexes take off the indexes, do the import, and then reassert the indexes. Faster that way. You only rebuild the indexes once.

Indexes take up space. For huge tables, you will have huge indexes. A down side of this is if you churn the tables as described above, you churn the indexes. This causes very rapid database bloat, thus forcing you to do lots of database Compaction And Repair operations.

If the index has a high cardinality (large number of records returned for a single value because the index is non-unique), there is a point of diminishing return on value. Rule of thumb and based STRICTLY on statistical concepts: If the number of records implied by a single value in an index is greater than the square root of the number of records in the entire table, the index isn't worth a damn. Some purists take that down to cube root, but that becomes a matter of personal preference. Just as a sanity check, an index having a cardinality of 1(.000...000) is a unique index. A unique index always has a cardinality of exactly 1. For the purposes of this factor, cardinality can never be fractional because the term implies "for any existing index entry." Don't confuse cardinality with a table's sparseness. I.e. if indexes cover a range but the range is not uniformly represented, the probability of returning a record might be less than 1 - but the table's cardinality would still be at least 1 no matter what you did.

If the indexed fields are too long then you aren't helping yourself either. Indexes should be based on small fields. Like INTEGER or LONG, or on very short TEXT fields. See, the issue here is BUFFER SIZE. Buffers are fixed length in Access. Very long index keys means fewer indexes fit in that fixed-length buffer. Fewer keys per buffer means more disk I/O per search. Given that computers are now in the 2+ GHz range but disk seeks are still in the 30 to 120 Hz range, one seek costs you up to 100 million instructions (that you could have been performing instead of seeking for disk data.)

There are purists who might suggest that if the indexed fields account for more than 1/2 of the table, some of them are a waste. Myself, I'd go along with 1/3 of table size or even 1/4 as the limit.

This is also why some folks vote for having autonumber prime keys. Size of those is fixed at 4 bytes plus the pointer overhead. This is usually good for several hundred indexes per buffer. This makes disk searches lots more efficient and effective.

 

[gear]

Thank you Sir. This is a real eye-opener. Hope this will be helpful for others also.

 

[The_Doc_Man]

Glad to help when I can.