informix uses bogo sort
This has probably come up before but it got me peeved so I thought
I'd post it anyway.
--
Conclusive proof that Informix uses bogo sort !!
Table: time_records cases
Row size: 84 775
Columns: 14 32
# records: 214613 10318
Indexes :
time_records.client - duplicates
time_records.case_no - duplicates
cases.case_no - unique
statement #1
select time_records.*, case_name from time_records, outer cases
where time_records.client = "AA1000"
and time_records.case_no = cases.case_no
returns 34 rows in 0.48 sec
statement #2
select time_records.*, case_name from time_records, outer cases
where time_records.client = "AA1000"
and time_records.case_no = cases.case_no
order by time_records.case_no
returns 34 sorted rows in 5 min 42.05 sec !!!!!
statement #3
select time_records.*, case_name from time_records, outer cases
where time_records.client = "AA1000"
and time_records.case_no = cases.case_no
into temp foo;
select * from foo
order by case_no
returns 34 sorted rows in 0.59 sec
this happens using V4.00 informix + V4.00 SE on both ICL drs6000 and IBM rs6000
I think I will be using method #3 whenever I want anything sorted in future !!
Mark Kellaway
--
wot no sig