From 4b513347c4c5ca72c13ec42c499d243c0dfdcd1f Mon Sep 17 00:00:00 2001 From: "monty@hundin.mysql.fi" <> Date: Thu, 29 Nov 2001 15:34:37 +0200 Subject: Better optimization for InnoDB and BDB tables for ORDER BY --- sql/ha_berkeley.h | 1 + sql/ha_innobase.h | 1 + sql/handler.h | 1 + sql/sql_select.cc | 7 ++++++- 4 files changed, 9 insertions(+), 1 deletion(-) (limited to 'sql') diff --git a/sql/ha_berkeley.h b/sql/ha_berkeley.h index 561e06229fa..ab1ead5a3e9 100644 --- a/sql/ha_berkeley.h +++ b/sql/ha_berkeley.h @@ -107,6 +107,7 @@ class ha_berkeley: public handler uint extra_rec_buf_length() { return BDB_HIDDEN_PRIMARY_KEY_LENGTH; } ha_rows estimate_number_of_rows(); bool fast_key_read() { return 1;} + key_map keys_to_use_for_scanning() { return ~(key_map) 0; } bool has_transactions() { return 1;} int open(const char *name, int mode, uint test_if_locked); diff --git a/sql/ha_innobase.h b/sql/ha_innobase.h index 95bba76c842..83e43b1d662 100644 --- a/sql/ha_innobase.h +++ b/sql/ha_innobase.h @@ -101,6 +101,7 @@ class ha_innobase: public handler bytes */ uint max_key_length() const { return 7000; } bool fast_key_read() { return 1;} + key_map keys_to_use_for_scanning() { return ~(key_map) 0; } bool has_transactions() { return 1;} int open(const char *name, int mode, uint test_if_locked); diff --git a/sql/handler.h b/sql/handler.h index f6e9ad61d94..560420a480d 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -223,6 +223,7 @@ public: { return ulonglong2double(data_file_length) / IO_SIZE + 1; } virtual double read_time(ha_rows rows) { return rows; } virtual bool fast_key_read() { return 0;} + virtual key_map keys_to_use_for_scanning() { return 0; } virtual bool has_transactions(){ return 0;} virtual uint extra_rec_buf_length() { return 0; } virtual ha_rows estimate_number_of_rows() { return records+EXTRA_RECORDS; } 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 90004e340b82413d0058b9eaebb4d776938aa00f Mon Sep 17 00:00:00 2001 From: "monty@hundin.mysql.fi" <> Date: Wed, 5 Dec 2001 23:15:05 +0200 Subject: Fixed bug in LEFT JOIN --- sql/sql_select.cc | 22 ++++++++++++++++------ 1 file changed, 16 insertions(+), 6 deletions(-) (limited to 'sql') 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