From 573eb1dec4a96d304b1abf4f2cf50236f58f6409 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 29 Nov 2001 15:34:37 +0200 Subject: Better optimization for InnoDB and BDB tables for ORDER BY Build-tools/Do-compile: Merge with 4.0 Docs/manual.texi: Changelog mysql-test/r/innodb.result: New tests cases for ORDER BY mysql-test/r/myisam.result: New tests cases for ORDER BY mysql-test/t/innodb.test: New tests cases for ORDER BY mysql-test/t/myisam.test: New tests cases for ORDER BY --- sql/sql_select.cc | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 774be3679a2..9456211a7cc 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5199,7 +5199,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit) retrieving all rows through an index. */ if (select_limit >= table->file->records) - keys&= table->used_keys; + keys&= (table->used_keys | table->file->keys_to_use_for_scanning()); for (nr=0; keys ; keys>>=1, nr++) { @@ -5213,6 +5213,11 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit) join_init_read_last_with_key); table->file->index_init(nr); tab->type=JT_NEXT; // Read with index_first(), index_next() + if (table->used_keys & ((key_map) 1 << nr)) + { + table->key_read=1; + table->file->extra(HA_EXTRA_KEYREAD); + } DBUG_RETURN(1); } } -- cgit v1.2.1 From 8cc4b2dd26b16e5b8aa278f56e345182f0413a8b Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 5 Dec 2001 23:15:05 +0200 Subject: Fixed bug in LEFT JOIN Docs/manual.texi: ChangeLog myisam/mi_locking.c: Remove wrong optimistic locking. (Doesn't affect MySQL) mysys/my_init.c: Release character sets at end scripts/mysqlhotcopy.sh: RAID tables are now copied --- sql/sql_select.cc | 22 ++++++++++++++++------ 1 file changed, 16 insertions(+), 6 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 9456211a7cc..330df610b46 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2119,10 +2119,17 @@ get_best_combination(JOIN *join) j->type=JT_REF; /* Must read with repeat */ else if (ref_key == j->ref.key_copy) { /* Should never be reached */ - j->type=JT_CONST; /* purecov: deadcode */ + /* + This happen if we are using a constant expression in the ON part + of an LEFT JOIN. + SELECT * FROM a LEFT JOIN b ON b.key=30 + Here we should not mark the table as a 'const' as a field may + have a 'normal' value or a NULL value. + */ + j->type=JT_CONST; if (join->const_tables == tablenr) { - join->const_tables++; /* purecov: deadcode */ + join->const_tables++; join->const_table_map|=form->map; } } @@ -2251,7 +2258,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) used_tables|=current_map; if (tab->type == JT_REF && tab->quick && - tab->ref.key == tab->quick->index && + (uint) tab->ref.key == tab->quick->index && tab->ref.key_length < tab->quick->max_used_key_length) { /* Range uses longer key; Use this instead of ref on key */ @@ -2689,8 +2696,8 @@ static void update_depend_map(JOIN *join, ORDER *order) /* -** simple_order is set to 1 if sort_order only uses fields from head table -** and the head table is not a LEFT JOIN table + simple_order is set to 1 if sort_order only uses fields from head table + and the head table is not a LEFT JOIN table */ static ORDER * @@ -4331,8 +4338,11 @@ join_read_const(JOIN_TAB *tab) } store_record(table,1); } - else if (!table->status) // Only happens with left join + else if (!(table->status & ~STATUS_NULL_ROW)) // Only happens with left join + { + table->status=0; restore_record(table,1); // restore old record + } table->null_row=0; return table->status ? -1 : 0; } -- cgit v1.2.1