Primary key, Foreign key and indexes in Informix

Dear All, We r using informix 9.21 on solaris. We have a small database of 1.5GB with 100 tables. I have staretd analysing the appliaction queries which have written in the past. I have few questions regarding PK,FK and index creation in informix. 1. When i create a Primary key it creates an index automatically. Do i need to create explicit index for better performance? 2. When i create a foreign key it also creates an index, do i need to create an index on the foreign key also explicitly , if it is used in where cluase etc. 3. Will it imprve the performance of the queries if we create a explicit indexes . 4. If i am using 4 columns in one query which runs very frequently, and one of the same column i use in another qury, and another column in another query. In this case is it better to create a composite index on all 4 columns , and separate indexes on each column? Any suggestions highly appreciated.. Thanks in Advance Kpai

"KalpanaPai" <kalpanapai@hotmail.com> wrote in message news:8b77f6f5.0404271611.6a7f21ac@posting.google.com... > Dear All, > > We r using informix 9.21 on solaris. > We have a small database of 1.5GB with 100 tables. > > I have staretd analysing the appliaction queries which have written > in the past. I have few questions regarding PK,FK and index creation > in informix. > > 1. When i create a Primary key it creates an index automatically. Do i > need to create explicit index for better performance? PKY creates index automatically. Trying to create index on the same column will result in error, unless of course you create it as a DESC index. > > 2. When i create a foreign key it also creates an index, do i need to > create an index on the foreign key also explicitly , if it is used in > where cluase etc. Same as PKY. FKY will create an index for you. > 3. Will it imprve the performance of the queries if we create a > explicit indexes . Moot. > 4. If i am using 4 columns in one query which runs very frequently, > and one of the same column i use in another qury, and another column > in another query. In this case is it better to create a composite > index on all 4 columns , and separate indexes on each column? assuming the columns to be indexed are a,b,c,d in this case composite index is worthless for those queries which uses column b or c or d. Composite index will work only for WHERE a = jujuju AND b = jujuju AND c = jujuju AND d = jujuju. composite index will also work for this query WHERE a = jujuju because column a is the leading column in this index. so for a query like WHERE b = jujuju, there will be no index scan. same for column c and d. You have no other choice but to create separate index. I would also suggest creating a composite index and individual index as required. For all said and done, composite index perform order of magnitude better than separate index for queries like WHERE a = jujuju AND b = jujuju AND c = jujuju AND d = jujuju.

KalpanaPai wrote: > > 1. When i create a Primary key it creates an index automatically. Do i > need to create explicit index for better performance? > > 2. When i create a foreign key it also creates an index, do i need to > create an index on the foreign key also explicitly , if it is used in > where cluase etc. > > 3. Will it imprve the performance of the queries if we create a > explicit indexes . As you've observed, the engine will create a hidden index if it cannot find an existing explicit index that will cover the task. I find that it is a little bit better administratively to create an explicit index first and then let the primary or foreign keys "piggy back" on the existing indexes. The same also goes for a UNIQUE constraint attached to columns, by the way. As for performance, an index is an index. If the engine can use it then it doesn't matter where the index comes from. The will be no performance difference from using an explicit index, or depending on the implicit index. There is, however, a potential problem with foreign key indexes, and it comes about due to an effect that causes trouble with any indexes that have a very poor data distribution. To understand the problem you need to know a bit about the internal structure of an index. For every unique value in an index, the engine stores one copy of the value (this can include a set of more than one field; it doesn't matter how the stored value is constructed). Then for every value "inserted" into the index, if the value already exists in the index then only the address of the new row is stored. All of the addresses are stored in a linked list of rowids that are attached to the key value. If you insert 1, 1, 2, 2, 1, 3, 2, 3 into a table and there is an index on that column, then there will be 3 entries for (1), 3 entries for (2), and 2 entries for (3). These will be stored something like this [this diagram is very simplified]: 1: rowid1 rowid2 rowid5 2: rowid3 rowid4 rowid7 3: rowid6 rowid8 If you insert or delete a row then the engine must find the exact rowid in the list for that value. If there is only a few values then that search is extremely quick, since many fit on one page. However, when the number of rows sharing the same key approaches hundreds, then the search for the rowid can cause quite a few page reads and will take a lot of time. Also, the adjustment of the list to fill in the gaps will probably take a lot of time [If anyone can describe engine optimisations here I'd be most interested to read] Therefore, if you are going to put an index onto a column or set of columns that has a large number of duplicates, then you would probably be better off NOT using the index. A good example of a bad index would be to index the State field of a customer order table in the US - perhaps for IBM customer orders, for example. Let's say there are 50 US states (close enough) and there are 200,000 customer orders in the table at any one time. On average there will be 200,000 / 50 rows all sharing the same index value, which means there will be on average 4000 rows attached to one key. Given the population spread of America, there would probably be 50,000 orders for the states of CA and NY each. That is a very large list of rowids to be stored in an index. Therefore, every time an order is added or removed from the table, there will be a lot of hard work to be done adjusting the index. This index would be A Very Bad Idea. Now, if you are a Relational Database Purist, you might insist that your order table MUST have a foreign key on the state table. If you believe this, you will suffer. It is just as easy (generally) to make the programs check these relationships. To get the best from FKs, I suggest that you should use as many foreign keys as you need in a development database, but you should remove the bad FK and index definitions from production databases. If you do adequate testing in development then any software faults should be caught before it ever gets to production. At the very least, the existence of the FK in development is a very strong hint to the programmers that they need to validate the state field. > 4. If i am using 4 columns in one query which runs very frequently, > and one of the same column i use in another qury, and another column > in another query. In this case is it better to create a composite > index on all 4 columns , and separate indexes on each column? If an index exists on columns (A,B,C,D) then the engine can use the index on queries that refer to (A,B,C,D) or (A,B,C) or (A,B) or (A) columns. So I try to order (A,B,C,D) in a way that can be useful for other queries. If you also want to query by column C only, then you may need an index on (C). On the other hand, the engine can do other query paths that don't necessarily need indexes. This applies especially when you are selecting large parts of the tables involved in the joins. The engine can either make a temporary index, or perform a sort operation at Just The Right Time, or it can perform a hash-join. The easiest way to know if an index is useful is to try it and see what happens. With experience you'll be able to make better guesses in the future without needing to test every time.