Re: index duplicates PK, is it redundant?

Neil Truby wrote: >> >> No, it's not useless. The PK will "piggy-back" onto the index of the >> same shape. > > So you're saying that the engine will not create another index of the > same construction, and you won't suffer the additional overhead of > maintaining one extra physical index? Bang on. Additionally, you make a modest gain for administration as I've described and that's enough for me to prefer an explicit index first with a piggy-back PK. The same sort of deal happens with FK's by the way. They also require an index on the columns that relate to the PRIMARY table. However some foreign keys are very very poor candidates for indexing, so in a production environment you may need to make a pragmatic decision to drop a FK relationship in the interests of decent performance. In development, it's about a thousand times more important to get debugging effects over performance, so I say, put in every FK you can think of into development. The bad ones are ones that have a lot of duplication in the table containing the FK. For instance, imagine you have a US state reference table; the first field is the state code eg CA, IL, etc, and the 2nd field can be a description. A simple enough reference table. Now, you might have an invoice table with several hundred thousand rows at least. Let's say you apply a FK relationship to the state table. This requires an index to be built on the state column of invoice table. If the table has 500,000 rows and there are 50 states (give or take a Carolina or two) then on average there will be 10,000 rows in the invoice table for each state. Some states will of course be more highly represented... Now, 10,000 identical elements in an index are basically stored by storing one copy of the key value (eg 'CA' ) and then a linked list of the row identifiers (not exactly the rowid) which means a linked list of 10,000 elements. Managing a linked list of 10,000 elements is not a fast thing. If you ever modify the state, or indeed delete a row from the invoice table, count on it taking a large amount of time to find each pointer for each row. Deleting 10,000 elements for example during a purge, could take a large part of a week to complete... I think Informix should add the option of an index-free foreign key relationship. That needs some explanation: Firstly, the reference table containing the PK will by necessity be a unique index. So that's fine. Welcome aboard. The uniq index on the PK is necessary and sufficient for a very fast check that the column in the foreign table exists in the primary table. Just from that, you might wonder why the index is needed on the FK fields in the first place. It's used whenever you attempt to delete a row from the primary table. That is only legal if there is no related row in another table. Therefore an index on the FK fields should be a quick way to find if any related rows exist. Well, it would be fast to find, but not frikken fast if you've defined cascaded delete and there's a huge number of duplicates. Perhaps FK indexes could be designed to optionally contain only the key value and a count of referents. In that case, it will be very fast to maintain, still very fast to find a relationship, and if you get silly with a cascaded delete, well, the engine can do a sequential scan almost as quickly as using a very poorly distributed index. By the same argument, I think there's room to optionally declare a FK relationship to be implemented completely without an index on the FK side. Thankfully, an FK relationship between a pair of header/detail tables, such as an invoice and the line items, will have a very nice index since on average the FK index will only have as many duplicates as there are line items per invoice, for example 10 on average.