summary: Help on table indexing needed

Thank You to all, who answered me to my question. I asked the following: . I have a informix (online) table with the following scheme. . It contains the data of patient's stay in our hospital. . . ( . izahl char(10) not null, # the identification of the . patient . station char(4) not null, # the ward . a_datum date not null, # the start-date in the hospital . e_datum date, # the leaving date . ..... some other stuff deleted ....... . ); . . My main query is to ask: . which person were at a day or at a time-interval on station xx . In Informix 4GL the code is: . let ss = "select * from stationaer", . " where ", . " (stationaer.e_datum is null or stationaer.e_datum >= ?)", . " and stationaer.a_datum <= ? ", . " and stationaer.station = ?" . . This code is rather slow and I don't not know how to set the indices. . I indexed on every of this 4 field, but I seem the query resolves in . a . sequential search. INFORMIX-VERSION is 4.1. . Can You help me please. Thank You to: alain@tintin.csl.sni.be (Alain LEROY) "Peter H. Schmidt" <peterh@sun.gup-bn.GuP.DE> spw@sur1a.ess.harris.com (Steve Wallick) Jack Parker <jparker@hpbs2561.boi.hp.com> ckipp@vth1.vth.colostate.edu (Cathy Kipp) johnl@informix.com (Jonathan Leffler) alan@po.den.mmc.com (Alan Popiel) Graeme Sargent <graeme@pyra.co.uk> Robert Minter <rob@dssmktg.com> "Jim Barrick" <jimb@keynote.com> The main opinion was to install a combined index. I installed it on station and e_datum. Also I used the proposed query with "union" instead of "or". The search time was reduced, but I think it could even be faster. Perhaps, the query is not so good and i should divide it into two with use of a temp table. But I don't know. Appended, You will find the letters to me: ################# First : Indexing is one thing BUT the optimizer has to know the status of you database. To do it type in the following command "UPDATE STATISTICS". WARNING : It can take a _LONG_ time if you've never done it. Second: To know exactly what the optimizer do, use the command "SET EXPLAIN ON". It creates a log file, in the current directory, in which it traces the estimated cost of a query. That way, I'ts relatively easy to optimize it. Third: If you ONLY use that query (I doubt !), you can index on (a_datum, sation, e_datum) in that order. Try to do it and, please, let me know. ######################### Voraussichtlich erfolgreich ist ein kombinierter Index auf station und a.datum. Dieser sollte dem Optimizer als hochwertig vorkommen und daher ist zu erwarten, dass dieser genutzt wird. Die so ueber Index zu findende voraussichtlich kleine (Anzahl der taeglichen Einlieferunge je Station) Ergebnissmenge kann dann mit dem Filter 'e is not Null or e.datum >= ?' schnell bearbeitet werden. ################### I have a similar query involving a span of time that is bounded by two dates. I have found the best improvement by using UPDATE STATISTICS regularly. Assuming that you have already done this, try dropping all indices on the table and build one compound index encompasing a_datum, e_datum and station. If this helps build the other indices so that they are behind the compound index. ################## The first thing is the OR - some versions of the engine do not handle it well try: let ss = "select * from stationaer", " where ", " stationaer.e_datum is null ", " and stationaer.a_datum <= ? ", " and stationaer.station = ?", " UNION", " select * from stationaer", " where ", " stationaer.e_datum >= ?", " and stationaer.a_datum <= ? ", " and stationaer.station = ?" I don't know what how your fields contain (range of values - how many rows/day...), but choose 1 to 3 (MAX) fields as the indices - and make these the fields that vary the most. Station - for example - is probably the smallest group of values - so don't index that, Personally I would try indexing the dates only for this query. But - you can always experiment - try them in SQL first. It appears as though you know about SET EXPLAIN ON, so I won't go into that. ####################### Something to keep in mind about indexing, is that you should make your indexes as unique as possible in order to allow the engine to function more efficiently. -> ( -> izahl char(10) not null, # the identification of the patient -> station char(4) not null, # the ward -> a_datum date not null, # the start-date in the hospital -> e_datum date, # the leaving date -> ..... some other stuff deleted ....... -> ); ->In Informix 4GL the code is: -> let ss = "select * from stationaer", -> " where ", -> " (stationaer.e_datum is null or stationaer.e_datum >= ?)", -> " and stationaer.a_datum <= ? ", -> " and stationaer.station = ?" Guessing what your needs might be from what I see here, I might create the following indexes. Izahl is added to the composite index to cut down on the number of duplicate values - this can make your database more efficient. create unique index i_stat_iz on stationaer (izahl); create index i_stat_aeis on stationaer (a_datum, e_datum, station, izahl); Don't forget that only one index will be used, so creating composite indexes can be very useful. If you create a single index for each field, only one will be used. Since you ended up with a sequential search, you might want to play around with composite indexes a little. I hope this helps! Please feel free to write if I can answer any more specific questions for you. ################### I haven't conducted the tests, but I'd create an index: create index i1_stationaer on stationaer(station, a_datum); One change which would simplify things would be to have some date such as 31/12/9999 as the e_datum value for people still in hospital. The SELECT then simplifies to: select * from stationaer where e_datum >= ? and a_datum <= ? and station = ? and you might get some value out of extending i1_stationaer to include e_datum. ################## The "or" in your query is probably the culprit. "Ors" are handled badly by Informix and usually result in a sequential search of the entire table. One option is to rewrite your query as the UNION of two queries, one with "stationaer.e_datum is null" and one with "stationaer.e_datum >= ?". Another is to recast the "or" using a variation of DeMorgan's theorem: (A or B) == not (not A and not B) So I suppose this becomes: "not (stationaer.e_datum is not null and stat