more on primary key and index

Hi all: I am quiet confused on keys and database designs. I have a series of questions. I have read the manuals and books but I am still confused, so here it goes. Let's say I have a table with 10 fields. 3 of the fields combied together have a unquie identity, the rest are random. let's call the first three fields state, company, policytype. Now, queries will be submitted based on each one of those, or any two of those, or three of those together, something like below: select * from tbl where state="AL" select * from tbl where state="AL" and company="05" select * from tbl where state="AL" and company="05" andpolicytpe="Special" any way, u get the point. Let's say we are going to make things simple and NOT normalize it for now, and just this table by itself. By the way, I don't care much about the speed of the insert/delete/update, because the data only changes once per month, this is a DSS app but not a OLTP app. The only thing I care about is query speed. here comes the questions, I am going to offer what I think of it, but I am not sure of most of them. 1. is it necessary to create a serial field as a unique index of integers? I don't see how that could help the performance of the query being this is one table with no relationships. 2. let's say we do create a key field, is there a difference between making it a unique index or a primary key? I am not sure about this one. I don't see too much a difference between unique index and primary key. 3. if we make a primary key out of the first three fields, is it going to help the query? hmm, now I am confused. Does primary key help the query speed? I read it in the book that primary key is really something to uniquely identify a entity, and it's best to keep it as short as possible, such as a autonumber integer. Is that correct? if so, I don't see how that would benefit the query. However, when I set explain on, the primary key is displayed as being used the query. How is that? 4. What if I put a composit index on the first three fields? well, I am sure that's going to help the query. How does this compare the primary key approach? 5. What if I put a individual index on all three fields seperatly? How does this compare to the composite key approach? 6. What about cluster index? on each one? a composite cluster index on all three fields? 7. Somewhere in the books says if a field doesn't have many difference values, it's best not to index it. Let's say my table is 1 gig in size, and my company have only 5 different possible values to it, is it best not to index it? 8. Let's say I do break up the table and normalize it. I move all three fields to a different index table and make ids for it and make a foreign key for it in the main table. How much more speed can I get out of it? I apologize for so many questions, but they sort of go together in a way. Any answers to any one of these questions are greatly appreicated. laugh, I feel like I have a few more, but I can't think of them right now. Thanks a lot. yan

Yan, Answera follow.... Terry Hillick VP, CSCSi.com Yan Zhu wrote: > Hi all: > > I am quiet confused on keys and database designs. I have a series of > questions. I have read the manuals and books > but I am still confused, so here it goes. > > Let's say I have a table with 10 fields. 3 of the fields combied > together have a unquie identity, the rest are random. let's call the > first three fields state, company, policytype. Now, queries will be > submitted based on each one of those, or any two of those, or three of > those together, something like below: > > select * from tbl where state="AL" > select * from tbl where state="AL" and company="05" > select * from tbl where state="AL" and company="05" and> policytpe="Special" > > any way, u get the point. Let's say we are going to make things simple > and NOT normalize it for now, and just this table by itself. By the way, > I don't care much about the speed of the insert/delete/update, because > the data only changes once per month, this is a DSS app but not a OLTP > app. The only thing I care about is query speed. > > here comes the questions, I am going to offer what I think of it, > but I am not sure of most of them. > > 1. is it necessary to create a serial field as a unique index of > integers? No > I don't see how that could help the performance of the query > being this is one table with no relationships. > > 2. let's say we do create a key field, is there a difference between > making it a unique index or a primary key? With a primary key you can then implement referential integrity, via foreign keys, you will also be able to use cascading deletes, save on code for reference checking etc. > I am not sure about this one. I don't see too much a difference > between unique index and primary key. > > 3. if we make a primary key out of the first three fields, is it > going to help the query? The optimizer uses the existing indices to cut down on the amount of disk i/o necessaryto perform the query. Are you suggesting that you should do a sequential scan of every row in every table in your query (a parital Cartesion) join or are you asking if there is any benefit to a primary(unique) index over an index that allows dups? > hmm, now I am confused. Does primary key help the query speed? I > read it in the book that primary key is really something to uniquely > identify a entity, and it's best to keep it as short as possible, such > as a autonumber integer. Is that correct? > if so, I don't see how that would benefit the query. However, when I set > explain on, the primary key is displayed as being used the query. How is > that? > > 4. What if I put a composit index on the first three fields? It's a relational database, the physical relationship of the columns within a table do not matter. > > > well, I am sure that's going to help the query. How does this > compare the primary key approach? > > 5. What if I put a individual index on all three fields seperatly? The results would depend on several things. 1. The query and any joins that it contains. 2. An order by clause on the query. 3. The "distribution" of data in the columns and your UPDATE STATISTICS scheme. > > > How does this compare to the composite key approach? > > 6. What about cluster index? on each one? a composite cluster index > on all three fields? The cluster index will only help if you wanr to access the data in same physical order as the logical order. This cust down on disk access by increasing the likelyhood that the next row to be fetched will already be in the buffer or physical log. Since you load this data once per month it may pay you to look into pre-sorting the data prior to loading(I assume it's a complete refresh) this is effectively a cluster index. Asking Informix to perform a cluster index present several issues to consider. 1. You need enough disk space to hold twice the data. Informix will build an index base on your desired clustering and then will copy the table in that order. 2. For anything other than trivial amounts of data this can be a lengthy process. 3. The table will be locked during the process. > > > 7. Somewhere in the books says if a field doesn't have many > difference values, it's best not to index it. Let's say my table is 1 > gig in size, and my company have only 5 different possible values to it, > is it best not to index it? The manual also says not to index a table <100 rows. A 1GB table (million rows??) would take a long time to sequentially scan. A column only containing 5 differing values might be useful if the query contained a GROUP BY for that column, other than that a column with this kind of data should not be the first column in an index. If it was you would have a wide but shallow index and returning single rows based on a query would be comparatively slow as opposed to a narrow and deep strategy which would have the opposite effect. > > > 8. Let's say I do break up the table and normalize it. I move all > three fields to a different index table and make ids for it and make a > foreign key for it in the main table. How much more speed can I get out > of it? Depends on the value of the data, disk layout, memory usage buffers, etc. etc. The only way to know is to gain experience by testing it both ways. > > > I apologize for so many questions, but they sort of go together in a > way. Any answers to any one of these questions are greatly appreicated. > laugh, I feel like I have a few more, but I can't think of them right > now. > > Thanks a lot. > > yan

Yan Zhu wrote: > > Hi all: > > I am quiet confused on keys and database designs. I have a series of > questions. I have read the manuals and books > but I am still confused, so here it goes. > > Let's say I have a table with 10 fields. 3 of the fields combied > together have a unquie identity, the rest are random. let's call the > first three fields state, company, policytype. Now, queries will be > submitted based on each one of those, or any two of those, or three of > those together, something like below: > > select * from tbl where state="AL" > select * from tbl where state="AL" and company="05" > select * from tbl where state="AL" and company="05" and> policytpe="Special" > > any way, u get the point. Let's say we are going to make things simple > and NOT normalize it for now, and just this table by itself. By the way, > I don't care much about the speed of the insert/delete/update, because > the data only changes once per month, this is a DSS app but not a OLTP > app. The only thing I care about is query speed. > > here comes the questions, I am going to offer what I think of it, > but I am not sure of most of them. > > 1. is it necessary to create a serial field as a unique index of > integers? > I don't see how that could help the performance of the query > being this is one table with no relationships. > > 2. let's say we do create a key field, is there a difference between > making it a unique index or a primary key? > I am not sure about this one. I don't see too much a difference > between unique index and primary key. > > 3. if we make a primary key out of the first three fields, is it > going to help the query? > hmm, now I am confused. Does primary key help the query speed? I > read it in the book that primary key is really something to uniquely > identify a entity, and it's best to keep it as short as possible, such > as a autonumber integer. Is that correct? > if so, I don't see how that would benefit the query. However, when I set > explain on, the primary key is displayed as being used the query. How is > that? > > 4. What if I put a composit index on the first three fields? > > well, I am sure that's going to help the query. How does this > compare the primary key approach? > > 5. What if I put a individual index on all three fields seperatly? > > How does this compare to the composite key approach? > > 6. What about cluster index? on each one? a composite cluster index > on all three fields? > > 7. Somewhere in the books says if a field doesn't have many > difference values, it's best not to index it. Let's say my table is 1 > gig in size, and my company have only 5 different possible values to it, > is it best not to index it? > > 8. Let's say I do break up the table and normalize it. I move all > three fields to a different index table and make ids for it and make a > foreign key for it in the main table. How much more speed can I get out > of it? > > I apologize for so many questions, but they sort of go together in a > way. Any answers to any one of these questions are greatly appreicated. > laugh, I feel like I have a few more, but I can't think of them right > now. > > Thanks a lot. > > yan Given that this is a query system and referential integrity is not that important, you can dispense with the serial value as a primary key if you wish. It should never be queried on as it is persumably of no interest. Just index the three columns that are the real identifiers in one composite. That allows for queries that include all the index columns or the first two or the first one. If you want to query on the second and third or just the third, you will need to create indexes on these to make the query go via an index. Informix implements primary keys as unique indexes with all the component columns not allowing NULL. Additionally, you can refer to them implicitly in a foreign key constrant. In fact, Informix allows foreign keys to reference any unique index columns - this is an extension to ANSI. If you are still puzzled about what a primary key is, may I suggest you read a good book on the theory of relational databases? Chris Date has written a few that might be suitable. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 -- If all else fails, read the instructions and the release notes. Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/