MySQL Forums
Forum List  »  Optimizer & Parser

How to optimize a derived table required for re-ordering.
Posted by: Vikash Bucha
Date: November 25, 2006 05:58AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
How to optimize a derived table required for re-ordering.
3978
November 25, 2006 05:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.