Find Next row - Performance questions

I am in the process of porting our 4GL Development Product to INFORMIX, our first port to a Standard SQL RDBMS. Since our current file system uses a variation of the standard index file method. I have found it difficult to support all of our existing operations, and provide reasonable performance. The largest performance problem occurs when executing a logical next/prev record request. (To explain: a logical next record, given a key of N columns and a value, find the next record in key order) I currently have an implementation that is flexible enough to return the correct request for any table or key. I am looking for comments and suggestions on ways to improve performance. Algorithm: Next record is the minimum value of key for all rows with key values greater than the current value of key. For a key of one column: select min(lname) from maillist where lname > 'lebrun' However; in a real application the key is usually multiple columns. For a key of two columns: select lname, fname from maillist where lname = 'LeBrun' and fname = (select min(fname) from maillist where lname = 'LeBrun' and fname > 'John') select lname,fname from maillist where lname = (select min(lname) from maillist where lname > 'LeBrun') and fname = (select min(fname) from maillist where lname = (select min(lname) from maillist where lname > 'LeBrun')) In the above, lname is the primary column and fname is secondary column of the key. This example also assumes that (lname,fname) is a unique key, otherwise a third column "rowid" would be needed to determine uniqueness. For each column in key there is a need to select for each possible break level. With the possibility of up to 9 columns in a key, this method can get very costly. On way to increase the performance of the above query is to OR each break level query together and add an ORDER BY statement. The first record in the requesting table is the correct one, however; the rest of the resulting table would not necessary be of value. If all the break level queries are OR'ed together, would the string exceed the INFORMIX buffer size? Or any other INFORMIX limits? In the above example would INFORMIX buffer the information from each sub-query? Should I (is it possible) to remove the sub-queries? How many and on which columns should indexes be placed? create unique index on lname,fname OR create unique index on lname,fname create index on lname create index on fname OR ??? What if I generate a query which selects all records greater than the current one, ORDER BY 'key'. Will INFORMIX determine that a sort is not necessary? What about this approach, if the table is very large millions of rows? I have also needed to implement pervious, first and last. Thank you for reading this posting. If you can provide any assistance, please respond either by e-mail or post. I will post a summary of answers, if such can be done with this type of question. -- John LeBrun lebrun@admins.com Admins, Inc. (617) 494-5100 432 Columbia Ave. "Providing 4GL development tools Cambridge, MA since 1972"