Below is the list of changes that have just been committed into a local
5.2 repository of psergey. When psergey does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-09-15 19:05:18+04:00, sergefp@stripped +3 -0
BUG#30363: complex query slower by 2.6x to 6x times in 5.2 then in 5.1
The bugfix is a fix for the incorrect merge:
- Make make_join_orderinfo() determine the last table that can use the
join buffering in exactly the same way as it is done in
make_join_readinfo() in current 5.1
- Remove now unneeded code from make_join_readinfo()
mysql-test/r/innodb_mysql.result@stripped, 2007-09-15 19:05:12+04:00, sergefp@stripped +1 -1
BUG#30363: Updated (previously incorrect) test result
sql/sql_select.cc@stripped, 2007-09-15 19:05:13+04:00, sergefp@stripped +36 -28
BUG#30363: complex query slower by 2.6x to 6x times in 5.2 then in 5.1
The bugfix is a fix for the incorrect merge:
- Make make_join_orderinfo() determine the last table that can use the
join buffering in exactly the same way as it is done in
make_join_readinfo() in current 5.1
- Remove now unneeded code from make_join_readinfo()
sql/sql_select.h@stripped, 2007-09-15 19:05:13+04:00, sergefp@stripped +8 -1
BUG#30363: complex query slower by 2.6x to 6x times in 5.2 then in 5.1
- Added comments
diff -Nrup a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
--- a/mysql-test/r/innodb_mysql.result 2007-09-06 19:49:54 +04:00
+++ b/mysql-test/r/innodb_mysql.result 2007-09-15 19:05:12 +04:00
@@ -980,7 +980,7 @@ key PRIMARY
key_len 4
ref NULL
rows 16
-Extra
+Extra Using where; Using index
SELECT * FROM t2 WHERE b=1 ORDER BY a;
a b c
1 1 1
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc 2007-09-06 21:54:49 +04:00
+++ b/sql/sql_select.cc 2007-09-15 19:05:13 +04:00
@@ -7989,20 +7989,29 @@ static void push_index_cond(JOIN_TAB *ta
DBUG_VOID_RETURN;
}
+
+
+ /*
+ Determine if the set is already ordered for ORDER BY, so it can
+ disable join cache because it will change the ordering of the results.
+ Code handles sort table that is at any location (not only first after
+ the const tables) despite the fact that it's currently prohibited.
+ We must disable join cache if the first non-const table alone is
+ ordered. If there is a temp table the ordering is done as a last
+ operation and doesn't prevent join cache usage.
+ */
uint make_join_orderinfo(JOIN *join)
{
uint i;
+ if (join->need_tmp)
+ return join->tables;
+
for (i=join->const_tables ; i < join->tables ; i++)
{
JOIN_TAB *tab=join->join_tab+i;
TABLE *table=tab->table;
- if ((table == join->sort_by_table &&
- (!join->order || join->skip_sort_order ||
- test_if_skip_sort_order(tab, join->order, join->select_limit,
- FALSE, &table->keys_in_use_for_order_by))
- //psergey-merge-todo: ^ check what should be instead of the above
- // FALSE! check the last argument!
- ) ||
+ if ((table == join->sort_by_table &&
+ (!join->order || join->skip_sort_order)) ||
(join->sort_by_table == (TABLE *) 1 && i != join->const_tables))
{
break;
@@ -8013,12 +8022,25 @@ uint make_join_orderinfo(JOIN *join)
/*
- ...
+ Plan refinement stage: do various set ups for the executioner
+
SYNOPSIS
make_join_readinfo()
- join
- options
- no_jbuf_after X.
+ join Join being processed
+ options Join's options (checking for SELECT_DESCRIBE,
+ SELECT_NO_JOIN_CACHE)
+ no_jbuf_after Don't use join buffering after table with this number.
+
+ DESCRIPTION
+ Plan refinement stage: do various set ups for the executioner
+ - set up use of join buffering
+ - push index conditions
+ - increment counters
+ - etc
+
+ RETURN
+ FALSE - OK
+ TRUE - Out of memory
*/
static bool
@@ -8026,7 +8048,6 @@ make_join_readinfo(JOIN *join, ulonglong
{
uint i;
bool statistics= test(!(join->select_options & SELECT_DESCRIBE));
- bool ordered_set= 0; //psergey-merge-todo: sort this out!
bool sorted= 1;
DBUG_ENTER("make_join_readinfo");
@@ -8038,23 +8059,10 @@ make_join_readinfo(JOIN *join, ulonglong
tab->read_record.table= table;
tab->read_record.file=table->file;
tab->next_select=sub_select; /* normal select */
-
- /*
- Determine if the set is already ordered for ORDER BY, so it can
- disable join cache because it will change the ordering of the results.
- Code handles sort table that is at any location (not only first after
- the const tables) despite the fact that it's currently prohibited.
- We must disable join cache if the first non-const table alone is
- ordered. If there is a temp table the ordering is done as a last
- operation and doesn't prevent join cache usage.
+ /*
+ TODO: don't always instruct first table's ref/range access method to
+ produce sorted output.
*/
- if (!ordered_set && !join->need_tmp &&
- ((table == join->sort_by_table &&
- (!join->order || join->skip_sort_order)) ||
- (join->sort_by_table == (TABLE *) 1 && i != join->const_tables)))
- ordered_set= 1;
-
-
tab->sorted= sorted;
sorted= 0; // only first must be sorted
if (tab->insideout_match_tab)
diff -Nrup a/sql/sql_select.h b/sql/sql_select.h
--- a/sql/sql_select.h 2007-09-04 22:30:37 +04:00
+++ b/sql/sql_select.h 2007-09-15 19:05:13 +04:00
@@ -242,6 +242,7 @@ typedef struct st_join_table {
uint used_fields,used_fieldlength,used_blobs;
enum join_type type;
bool cached_eq_ref_table,eq_ref_table,not_used_in_distinct;
+ /* TRUE <=> index-based access method must return records in order */
bool sorted;
/*
If it's not 0 the number stored this field indicates that the index
@@ -403,7 +404,13 @@ public:
JOIN_TAB *join_tab,**best_ref;
JOIN_TAB **map2table; // mapping between table indexes and JOIN_TABs
JOIN_TAB *join_tab_save; // saved join_tab for subquery reexecution
- TABLE **table,**all_tables,*sort_by_table;
+ TABLE **table,**all_tables;
+ /*
+ The table which has an index that allows to produce the requried ordering.
+ A special value of 0x1 means that the ordering will be produced by
+ passing 1st non-const table to filesort(). NULL means no such table exists.
+ */
+ TABLE *sort_by_table;
uint tables; /* Number of tables in the join */
uint outer_tables; /* Number of tables that are not inside semijoin */
uint const_tables;
| Thread |
|---|
| • bk commit into 5.2 tree (sergefp:1.2599) BUG#30363 | Sergey Petrunia | 15 Sep |
