How to optimize a derived table required for re-ordering.

[Vikash Bucha]

I have a query like this, on a MyISAM table.

1: INNER_QUERY
SELECT UDF(fieldx,fieldy) AS final_sort_order,field1,field2,.....field20 FROM table_name WHERE where_condition ORDER BY sort_field DESC;

where UDF is a function that returns some re-sorting weights.

2: FINAL_QUERY
SELECT * FROM ((INNER_QUERY) AS x) ORDER BY final_sort_order ASC;

Time difference between Query 1 and Query 2 is negligible when there is a smaller dataset to return. (by reducing the rows through where_clause or taking off some of the fields field1,field2....)

But when the dataset is large, the difference is more than double. For this resultset, Query 2 also increments the "Created_tmp_disk_tables" variable by 1.

I tried incrementing the "tmp_table_size = 3064M", which I think is really large enough, but that doesn't help either.
and should not be required at all for the outer select. In fact it is just a reordering on the original resultset obtained from INNER_QUERY.

BTW: CREATE TABLE xyz FINAL_QUERY;
for the worst case produces an xyz.myd of less than 12MB.

Any help is highly appreciated.



Edited 1 time(s). Last edit at 11/25/2006 06:20AM by Vikash Bucha.