Re: SQL Optimizing- Indexes and Order By clause

On Jul 9, 8:22 pm, "Avichal Narayan" <avichal.nara...@gmail.com> wrote: > Hi > > I have a simple SQL which iam trying to optimize. > > select debt_code, debt_surname, debt_firstnames > from debtor > where debt_surname matches "Smith" > order by debt_surname, debt_firstnames> > I have a composite index on debt_surname, debt_firstnames on debtor table. > I also have a unique index on debt_code in debtor table. > > Questions: > > If i dont put the order by clause, will i still get the same results as with > order by clause since index is created. > If not whats the best way to optimize this sql. The debtor table contains > some 18million records. If i put a surname that returns maybe 20-30 rows is > much faster. But if i use the above surname-"Smith" which contains some 3 > million records it gets really slow. > > I am using this in I4GL and only going to select the first 300 sorted > records into the array. So i need it to be sorted. > > Any assistance will be appreciated. > > TIA > > Avichal Yes, use the first clause. If you are paging through a list of debtors 300 at a time then the skip clause will also come in handy. select first 300 debt_code, debt_surnmae, debt_firstnames from debtor where debt_surname matches "Smith" order by debt_surname, debt_firstnames, debt_code -- needed for uniqueness of order if both names match.; select first 300 debt_code, debt_surnmae, debt_firstnames from debtor where debt_surname matches "Smith" order by debt_surname, debt_firstnames, debt_code -- PK;