>I've a table with 150 000 records. > > DEL = 1 - about 100 records > DEL = 0 - about 149 900 records > > What can I do to speed up this query ? Without ORDER it runs in about 1.5 s.
Returning only 500 records, it is probably better not to use the index for ANAME. You might change your query to: SELECT ID, ANAME FROM MYTABLE WHERE ANAME LIKE '%TEST%' AND DEL+0 = 0 ORDER BY ANAME||'' I don't know how much it will improve, I was surprised about you saying that it was 100 times quicker without the ORDER BY. Are you sure that you measured how much longer it took to return the entire result set, and not just the first few rows? I would expect SELECT count(*) FROM MYTABLE WHERE ANAME LIKE '%TEST%' AND DEL+0 = 0 to return quicker than your query could possibly finish (of course, transferring 500 integers and 500 varchar(250) takes longer than transferring one integer, the rest of the work should be similar). HTH, Set
