Re: Help with composite indices

Bradford Young <byoung@cornercap.com> wrote in article <3325CA42.67ED@cornercap.com>... > I've read how to create a composite index, but I'm unsure about > how to use one to order output or join tables. In my db, I have > tables with company id's and dates, with the combination of > id and date being unique. So I can create an index on (id, date). > Can I use that index for joining tables? > Can I use that index when I come to the "order by" part of a > select statement? The query optimizer will decide which indexes would best be utilized, based upon all of the tables involved in your query, the indexes on those tables, the statistics it has available (this varies by Informix version -- SE vs. Online as well as version number), the filters and joins in the where clause as well as the order by clause. In short, yes the index you propose MAY be used in joins and/or sorts, but it's not something you can directly control. If your queries are simple, the optimizer will usually do a good job picking the best query plan. For more complex queries (lots of tables -- and therefore lots of joins) it helps if you have used UPDATE STATISTICS HIGH/MEDIUM/LOW to create frequency distributions of the data in the tables (available with 6.x and later versions of the engine). Check out http://www.objectsoft.com (shameless plug) for a script which automatically performs an appropriate sequence of UPDATE STATISTICS commands for specified tables. -- Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com