Re: Select Performance Using Order By on Index

Try this : SELECT * FROM find WHERE find_field1 = 1 AND find_field2 = 5 AND find_field3 >= 1 union SELECT * FROM find WHERE find_field1 = 1 AND find_field2 > 5 union SELECT * FROM find WHERE find_field1 > 1 ORDER BY find_field1, find_field2, find_field3 The reason for the slow performance was the 'OR' infx will only use the index for the first condition and the do sequential scan for find_field1 = 1 AND find_field2 > 5 and find_field1 > 1. Using the union splits the selects so that each condition use an index but the output is still combined just like it would be one select. Mariusz Malogrosz mariuszm\\@tecsys.com T. Kramer 794-1472 (theo@rasdev.rascal.co.za) wrote: : Hi, : I have a peformance problem when using select and order by on a multi field : index that, hopefully, some of you SQL gurus can help me with. : The problem is as follows: : I need to select and fetch forwards in the natural index order where the index : contains multiple segments on a single data table. To illustrate the point : and for the sake of simplicity I will use numeric fields as follows: : field1 field2 field3 : ----------------------------- : 1 5 1 : 1 5 2 : 1 6 0 : 1 6 1 : 1 6 2 : 1 6 3 : 2 4 3 : The index I have created is unique and consists of all three fields in order. : The query that I use is as follows: : SELECT * FROM find WHERE : find_field1 = 1 AND find_field2 = 5 AND find_field3 >= 1 OR : find_field1 = 1 AND find_field2 > 5 OR : find_field1 > 1 : ORDER BY find_field1, find_field2, find_field3 : Doing this query on small tables gives reasonable performance, however, if I : do the query on large tables (approx 30000 records) and the required record : is towards the end of the index the select becomes unacceptably slow. Note that : the extent sizes on the table are correctly set up. : Changing the query to the following: : SELECT * FROM find WHERE : find_field1 >= AND find_field2 >= 5 AND find_field3 >= 1 : ORDER BY find_field1, find_field2, find_field3 : provides intantaneous response (so I do know that informix can do it) yet provides : an incorrect result set when scrolling forwards ie. the third and last record : are no longer part of the result set. Comparing the cost using 'explain' also : gives an entire different picture for the two queries ie. the first query is : much more expensive than the second, yet I feel that the second should be more : expensive as records in the natural index sequence have to be skipped. Note that : in both cases informix reports that it does use the index. : The questions I have are as follows: : 1. Why is this the case on a naturally ordered index? : 2. What can I do to improve the select performance with the result set : being what I require? : Thanks in advance, : Theo Kramer : theo@rascal.co.za