diff options
Diffstat (limited to 'sql/opt_sum.cc')
| -rw-r--r-- | sql/opt_sum.cc | 295 |
1 files changed, 175 insertions, 120 deletions
diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index f8603f06fa0..3ccc1e5cf41 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -14,7 +14,9 @@ Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ -/* +/** + @file + Optimising of MIN(), MAX() and COUNT(*) queries without 'group by' clause by replacing the aggregate expression with a constant. @@ -22,6 +24,7 @@ types of queries are optimised (assuming the table handler supports the required methods) + @verbatim SELECT COUNT(*) FROM t1[,t2,t3,...] SELECT MIN(b) FROM t1 WHERE a=const SELECT MAX(c) FROM t1 WHERE a=const AND b=const @@ -29,6 +32,7 @@ SELECT MIN(b) FROM t1 WHERE a=const AND b>const SELECT MIN(b) FROM t1 WHERE a=const AND b BETWEEN const AND const SELECT MAX(b) FROM t1 WHERE a=const AND b BETWEEN const AND const + @endverbatim Instead of '<' one can use '<=', '>', '>=' and '=' as well. Instead of 'a=const' the condition 'a IS NULL' can be used. @@ -36,10 +40,11 @@ If all selected fields are replaced then we will also remove all involved tables and return the answer without any join. Thus, the following query will be replaced with a row of two constants: + @verbatim SELECT MAX(b), MIN(d) FROM t1,t2 WHERE a=const AND b<const AND d>const + @endverbatim (assuming a index for column d of table t2 is defined) - */ #include "mysql_priv.h" @@ -54,24 +59,54 @@ static int maxmin_in_range(bool max_fl, Field* field, COND *cond); /* - Substitutes constants for some COUNT(), MIN() and MAX() functions. + Get exact count of rows in all tables SYNOPSIS - opt_sum_query() - tables list of leaves of join table tree - all_fields All fields to be returned - conds WHERE clause + get_exact_records() + tables List of tables + + NOTES + When this is called, we know all table handlers supports HA_HAS_RECORDS + or HA_STATS_RECORDS_IS_EXACT + + RETURN + ULONGLONG_MAX Error: Could not calculate number of rows + # Multiplication of number of rows in all tables +*/ + +static ulonglong get_exact_record_count(TABLE_LIST *tables) +{ + ulonglong count= 1; + for (TABLE_LIST *tl= tables; tl; tl= tl->next_leaf) + { + ha_rows tmp= tl->table->file->records(); + if ((tmp == HA_POS_ERROR)) + return ULONGLONG_MAX; + count*= tmp; + } + return count; +} + + +/** + Substitutes constants for some COUNT(), MIN() and MAX() functions. - NOTE: + @param tables list of leaves of join table tree + @param all_fields All fields to be returned + @param conds WHERE clause + + @note This function is only called for queries with sum functions and no GROUP BY part. - RETURN VALUES + @retval 0 no errors + @retval 1 if all items were resolved + @retval HA_ERR_KEY_NOT_FOUND on impossible conditions - OR an error number from my_base.h HA_ERR_... if a deadlock or a lock - wait timeout happens, for example + @retval + HA_ERR_... if a deadlock or a lock wait timeout happens, for example */ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) @@ -79,8 +114,8 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) List_iterator_fast<Item> it(all_fields); int const_result= 1; bool recalc_const_item= 0; - longlong count= 1; - bool is_exact_count= TRUE; + ulonglong count= 1; + bool is_exact_count= TRUE, maybe_exact_count= TRUE; table_map removed_tables= 0, outer_tables= 0, used_tables= 0; table_map where_tables= 0; Item *item; @@ -119,15 +154,18 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) used_tables|= tl->table->map; /* - If the storage manager of 'tl' gives exact row count, compute the total - number of rows. If there are no outer table dependencies, this count - may be used as the real count. + If the storage manager of 'tl' gives exact row count as part of + statistics (cheap), compute the total number of rows. If there are + no outer table dependencies, this count may be used as the real count. Schema tables are filled after this function is invoked, so we can't get row count */ - if ((tl->table->file->table_flags() & HA_NOT_EXACT_COUNT) || + if (!(tl->table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) || tl->schema_table) { + maybe_exact_count&= test(!tl->schema_table && + (tl->table->file->ha_table_flags() & + HA_HAS_RECORDS)); is_exact_count= FALSE; count= 1; // ensure count != 0 } @@ -137,9 +175,10 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) if(error) { tl->table->file->print_error(error, MYF(0)); + tl->table->in_use->fatal_error(); return error; } - count*= tl->table->file->records; + count*= tl->table->file->stats.records; } } @@ -161,9 +200,19 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) there are no outer joins. */ if (!conds && !((Item_sum_count*) item)->get_arg(0)->maybe_null && - !outer_tables && is_exact_count) + !outer_tables && maybe_exact_count) { - ((Item_sum_count*) item)->make_const(count); + if (!is_exact_count) + { + if ((count= get_exact_record_count(tables)) == ULONGLONG_MAX) + { + /* Error from handler in counting rows. Don't optimize count() */ + const_result= 0; + continue; + } + is_exact_count= 1; // count is now exact + } + ((Item_sum_count*) item)->make_const((longlong) count); recalc_const_item= 1; } else @@ -179,7 +228,7 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) Item *expr=item_sum->get_arg(0); if (expr->real_item()->type() == Item::FIELD_ITEM) { - byte key_buff[MAX_KEY_LENGTH]; + uchar key_buff[MAX_KEY_LENGTH]; TABLE_REF ref; uint range_fl, prefix_len; @@ -202,7 +251,7 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) const_result= 0; break; } - error= table->file->ha_index_init((uint) ref.key); + error= table->file->ha_index_init((uint) ref.key, 1); if (!ref.key_length) error= table->file->index_first(table->record[0]); @@ -227,9 +276,10 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) Closed interval: Either The MIN argument is non-nullable, or we have a >= predicate for the MIN argument. */ - error= table->file->index_read(table->record[0], ref.key_buff, - ref.key_length, - HA_READ_KEY_OR_NEXT); + error= table->file->index_read_map(table->record[0], + ref.key_buff, + make_prev_keypart_map(ref.key_parts), + HA_READ_KEY_OR_NEXT); else { /* @@ -238,8 +288,10 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) 2) there is a > predicate on it, nullability is irrelevant. We need to scan the next bigger record first. */ - error= table->file->index_read(table->record[0], ref.key_buff, - ref.key_length, HA_READ_AFTER_KEY); + error= table->file->index_read_map(table->record[0], + ref.key_buff, + make_prev_keypart_map(ref.key_parts), + HA_READ_AFTER_KEY); /* If the found record is outside the group formed by the search prefix, or there is no such record at all, check if all @@ -261,9 +313,11 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) (error == HA_ERR_KEY_NOT_FOUND || key_cmp_if_same(table, ref.key_buff, ref.key, prefix_len))) { - error= table->file->index_read(table->record[0], ref.key_buff, - ref.key_length, - HA_READ_KEY_EXACT); + DBUG_ASSERT(item_field->field->real_maybe_null()); + error= table->file->index_read_map(table->record[0], + ref.key_buff, + make_prev_keypart_map(ref.key_parts), + HA_READ_KEY_EXACT); } } } @@ -322,7 +376,7 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) Item *expr=item_sum->get_arg(0); if (expr->real_item()->type() == Item::FIELD_ITEM) { - byte key_buff[MAX_KEY_LENGTH]; + uchar key_buff[MAX_KEY_LENGTH]; TABLE_REF ref; uint range_fl, prefix_len; @@ -345,17 +399,17 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) const_result= 0; break; } - error= table->file->ha_index_init((uint) ref.key); + error= table->file->ha_index_init((uint) ref.key, 1); if (!ref.key_length) error= table->file->index_last(table->record[0]); else - error= table->file->index_read(table->record[0], key_buff, - ref.key_length, - range_fl & NEAR_MAX ? - HA_READ_BEFORE_KEY : - HA_READ_PREFIX_LAST_OR_PREV); - if (!error && reckey_in_range(1, &ref, item_field->field, + error= table->file->index_read_map(table->record[0], key_buff, + make_prev_keypart_map(ref.key_parts), + range_fl & NEAR_MAX ? + HA_READ_BEFORE_KEY : + HA_READ_PREFIX_LAST_OR_PREV); + if (!error && reckey_in_range(1, &ref, item_field->field, conds, range_fl, prefix_len)) error= HA_ERR_KEY_NOT_FOUND; if (table->key_read) @@ -370,6 +424,7 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) return HA_ERR_KEY_NOT_FOUND; // No rows matching WHERE /* HA_ERR_LOCK_DEADLOCK or some other error */ table->file->print_error(error, MYF(0)); + table->in_use->fatal_error(); return(error); } removed_tables|= table->map; @@ -425,19 +480,18 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) } -/* - Test if the predicate compares a field with constants +/** + Test if the predicate compares a field with constants. - SYNOPSIS - simple_pred() - func_item Predicate item - args out: Here we store the field followed by constants - inv_order out: Is set to 1 if the predicate is of the form - 'const op field' + @param func_item Predicate item + @param[out] args Here we store the field followed by constants + @param[out] inv_order Is set to 1 if the predicate is of the form + 'const op field' - RETURN + @retval 0 func_item is a simple predicate: a field is compared with - constants + constants + @retval 1 Otherwise */ @@ -509,34 +563,33 @@ bool simple_pred(Item_func *func_item, Item **args, bool *inv_order) } -/* - Check whether a condition matches a key to get {MAX|MIN}(field): +/** + Check whether a condition matches a key to get {MAX|MIN}(field):. - SYNOPSIS - matching_cond() - max_fl in: Set to 1 if we are optimising MAX() - ref in/out: Reference to the structure we store the key value - keyinfo in Reference to the key info - field_part in: Pointer to the key part for the field - cond in WHERE condition - key_part_used in/out: Map of matchings parts - range_fl in/out: Says whether including key will be used - prefix_len out: Length of common key part for the range - where MAX/MIN is searched for - - DESCRIPTION For the index specified by the keyinfo parameter, index that contains field as its component (field_part), the function checks whether the condition cond is a conjunction and all its conjuncts referring to the columns of the same table as column field are one of the following forms: - f_i= const_i or const_i= f_i or f_i is null, - where f_i is part of the index + where f_i is part of the index - field {<|<=|>=|>|=} const or const {<|<=|>=|>|=} field - field between const1 and const2 - RETURN + @param[in] max_fl Set to 1 if we are optimising MAX() + @param[in,out] ref Reference to the structure we store the key + value + @param[in] keyinfo Reference to the key info + @param[in] field_part Pointer to the key part for the field + @param[in] cond WHERE condition + @param[in,out] key_part_used Map of matchings parts + @param[in,out] range_fl Says whether including key will be used + @param[out] prefix_len Length of common key part for the range + where MAX/MIN is searched for + + @retval 0 Index can't be used. + @retval 1 We can use index to get MIN/MAX value */ @@ -616,17 +669,15 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, less_fl= 1-less_fl; // Convert '<' -> '>' (etc) /* Check if field is part of the tested partial key */ - byte *key_ptr= ref->key_buff; + uchar *key_ptr= ref->key_buff; KEY_PART_INFO *part; - for (part= keyinfo->key_part; - ; - key_ptr+= part++->store_length) + for (part= keyinfo->key_part; ; key_ptr+= part++->store_length) { if (part > field_part) return 0; // Field is beyond the tested parts if (part->field->eq(((Item_field*) args[0])->field)) - break; // Found a part od the key for the field + break; // Found a part of the key for the field } bool is_field_part= part == field_part; @@ -636,12 +687,15 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, key_part_map org_key_part_used= *key_part_used; if (eq_type || between || max_fl == less_fl) { - size_t length= (key_ptr-ref->key_buff)+part->store_length; + uint length= (key_ptr-ref->key_buff)+part->store_length; if (ref->key_length < length) + { /* Ultimately ref->key_length will contain the length of the search key */ - ref->key_length= (uint) length; + ref->key_length= length; + ref->key_parts= (part - keyinfo->key_part) + 1; + } if (!*prefix_len && part+1 == field_part) - *prefix_len= (uint) length; + *prefix_len= length; if (is_field_part && eq_type) *prefix_len= ref->key_length; @@ -664,15 +718,15 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, if (is_null) { part->field->set_null(); - *key_ptr= (byte) 1; + *key_ptr= (uchar) 1; } else { store_val_in_field(part->field, args[between && max_fl ? 2 : 1], CHECK_FIELD_IGNORE); if (part->null_bit) - *key_ptr++= (byte) test(part->field->is_null()); - part->field->get_key_image((char*) key_ptr, part->length, Field::itRAW); + *key_ptr++= (uchar) test(part->field->is_null()); + part->field->get_key_image(key_ptr, part->length, Field::itRAW); } if (is_field_part) { @@ -700,31 +754,21 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, } -/* +/** Check whether we can get value for {max|min}(field) by using a key. - SYNOPSIS - find_key_for_maxmin() - max_fl in: 0 for MIN(field) / 1 for MAX(field) - ref in/out Reference to the structure we store the key value - field in: Field used inside MIN() / MAX() - cond in: WHERE condition - range_fl out: Bit flags for how to search if key is ok - prefix_len out: Length of prefix for the search range - - DESCRIPTION - If where condition is not a conjunction of 0 or more conjuct the - function returns false, otherwise it checks whether there is an - index including field as its k-th component/part such that: - - 1. for each previous component f_i there is one and only one conjunct + If where-condition is not a conjunction of 0 or more conjuct the + function returns false, otherwise it checks whether there is an + index including field as its k-th component/part such that: + + -# for each previous component f_i there is one and only one conjunct of the form: f_i= const_i or const_i= f_i or f_i is null - 2. references to field occur only in conjucts of the form: + -# references to field occur only in conjucts of the form: field {<|<=|>=|>|=} const or const {<|<=|>=|>|=} field or field BETWEEN const1 AND const2 - 3. all references to the columns from the same table as column field + -# all references to the columns from the same table as column field occur only in conjucts mentioned above. - 4. each of k first components the index is not partial, i.e. is not + -# each of k first components the index is not partial, i.e. is not defined on a fixed length proper prefix of the field. If such an index exists the function through the ref parameter @@ -732,17 +776,26 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, the length of first (k-1) components of the key and flags saying how to apply the key for the search max/min value. (if we have a condition field = const, prefix_len contains the length - of the whole search key) + of the whole search key) + + @param[in] max_fl 0 for MIN(field) / 1 for MAX(field) + @param[in,out] ref Reference to the structure we store the key value + @param[in] field Field used inside MIN() / MAX() + @param[in] cond WHERE condition + @param[out] range_fl Bit flags for how to search if key is ok + @param[out] prefix_len Length of prefix for the search range - NOTE + @note This function may set table->key_read to 1, which must be reset after index is used! (This can only happen when function returns 1) - RETURN + @retval 0 Index can not be used to optimize MIN(field)/MAX(field) - 1 Can use key to optimize MIN()/MAX() - In this case ref, range_fl and prefix_len are updated -*/ + @retval + 1 Can use key to optimize MIN()/MAX(). + In this case ref, range_fl and prefix_len are updated +*/ + static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref, Field* field, COND *cond, @@ -786,6 +839,7 @@ static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref, { ref->key= idx; ref->key_length= 0; + ref->key_parts= 0; key_part_map key_part_used= 0; *range_fl= NO_MIN_RANGE | NO_MAX_RANGE; if (matching_cond(max_fl, ref, keyinfo, part, cond, @@ -811,6 +865,8 @@ static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref, /* Set the first byte of key_part_k to 1, that means NULL */ ref->key_buff[ref->key_length]= 1; ref->key_length+= part->store_length; + ref->key_parts++; + DBUG_ASSERT(ref->key_parts == jdx+1); *range_fl&= ~NO_MIN_RANGE; *range_fl|= NEAR_MIN; // Open interval } @@ -832,20 +888,19 @@ static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref, } -/* - Check whether found key is in range specified by conditions +/** + Check whether found key is in range specified by conditions. - SYNOPSIS - reckey_in_range() - max_fl in: 0 for MIN(field) / 1 for MAX(field) - ref in: Reference to the key value and info - field in: Field used the MIN/MAX expression - cond in: WHERE condition - range_fl in: Says whether there is a condition to to be checked - prefix_len in: Length of the constant part of the key + @param[in] max_fl 0 for MIN(field) / 1 for MAX(field) + @param[in] ref Reference to the key value and info + @param[in] field Field used the MIN/MAX expression + @param[in] cond WHERE condition + @param[in] range_fl Says whether there is a condition to to be checked + @param[in] prefix_len Length of the constant part of the key - RETURN + @retval 0 ok + @retval 1 WHERE was not true for the found row */ @@ -860,16 +915,16 @@ static int reckey_in_range(bool max_fl, TABLE_REF *ref, Field* field, } -/* - Check whether {MAX|MIN}(field) is in range specified by conditions - SYNOPSIS - maxmin_in_range() - max_fl in: 0 for MIN(field) / 1 for MAX(field) - field in: Field used the MIN/MAX expression - cond in: WHERE condition +/** + Check whether {MAX|MIN}(field) is in range specified by conditions. - RETURN + @param[in] max_fl 0 for MIN(field) / 1 for MAX(field) + @param[in] field Field used the MIN/MAX expression + @param[in] cond WHERE condition + + @retval 0 ok + @retval 1 WHERE was not true for the found row */ |