Okay, can someone explain this behavior? This is being run on Pervasive 9.5
select c1, c2, c3
where c4 = 'XXXX-XX-XX'
order by c2, c1, c3
The table involved has 3.5M records, there is an index on c4. If I run the query without the order by clause, the query completes within a minute and the monitor indicate that ~5000 records are read. If I run the query with the order by clause, the query takes several minutes to complete and the monitor indicates the 3,500,000 records were read.
It looks to me like the order by clause is ordering the entire table and then applying the where clause rather than using the index on c4 to generate results and ordering just the result.
Does this make sense to anyone?