From e0dbec1ce38688e25cab3720044d6e7e676381f6 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Fri, 2 Dec 2022 18:21:52 +0300 Subject: MDEV-29129: Performance regression starting in 10.6: select order by limit ... The cause of regression was handling for ROWNUM() function. For queries like SELECT ROWNUM() FROM ... ORDER BY ... ROWNUM() should be computed before the ORDER BY. The computation was moved to be before the ORDER BY for any entries in the select list that had RAND_TABLE_BIT set. This had a negative impact on queries in form: SELECT sp_func() FROM t1 ORDER BY ... LIMIT n where sp_func() is NOT declared as DETERMINISTIC (and so has RAND_TABLE_BIT set). The fix is to require evaluation for sorting only for the ROWNUM() function. Functions that just have RAND_TABLE_BIT() can be computed after ORDER BY ... LIMIT is applied. (think about a possible index that satisfies the ORDER BY clause. In that case, the the rows would be read in the needed order and we would stop after reading LIMIT rows, achieving the same effect). --- sql/sql_select.h | 5 ----- 1 file changed, 5 deletions(-) (limited to 'sql/sql_select.h') diff --git a/sql/sql_select.h b/sql/sql_select.h index c9e0fa25421..3332bc78b6c 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1420,11 +1420,6 @@ public: GROUP/ORDER BY. */ bool simple_order, simple_group; - /* - Set to 1 if any field in field list has RAND_TABLE set. For example if - if one uses RAND() or ROWNUM() in field list - */ - bool rand_table_in_field_list; /* ordered_index_usage is set if an ordered index access -- cgit v1.2.1