My Project
Functions
opt_sum.cc File Reference
#include "sql_priv.h"
#include "key.h"
#include "sql_select.h"

Functions

int opt_sum_query (THD *thd, TABLE_LIST *tables, List< Item > &all_fields, Item *conds)
bool simple_pred (Item_func *func_item, Item **args, bool *inv_order)

Detailed Description

Optimising of MIN(), MAX() and COUNT(*) queries without 'group by' clause by replacing the aggregate expression with a constant.

Given a table with a compound key on columns (a,b,c), the following types of queries are optimised (assuming the table handler supports the required methods)

  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
  SELECT MAX(b) FROM t1 WHERE a=const AND b<const
  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
  

Instead of '<' one can use '<=', '>', '>=' and '=' as well. Instead of 'a=const' the condition 'a IS NULL' can be used.

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:

  SELECT MAX(b), MIN(d) FROM t1,t2 
    WHERE a=const AND b<const AND d>const
  

(assuming a index for column d of table t2 is defined)


Function Documentation

int opt_sum_query ( THD *  thd,
TABLE_LIST tables,
List< Item > &  all_fields,
Item conds 
)

Substitutes constants for some COUNT(), MIN() and MAX() functions.

Parameters:
thdthread handler
tableslist of leaves of join table tree
all_fieldsAll fields to be returned
condsWHERE clause
Note:
This function is only called for queries with aggregate functions and no GROUP BY part. This means that the result set shall contain a single row only
Return values:
0no errors
1if all items were resolved
HA_ERR_KEY_NOT_FOUNDon impossible conditions
HA_ERR_...if a deadlock or a lock wait timeout happens, for example
ER_...e.g. ER_SUBQUERY_NO_1_ROW
bool simple_pred ( Item_func func_item,
Item **  args,
bool *  inv_order 
)

Test if the predicate compares a field with constants.

Parameters:
func_itemPredicate item
[out]argsHere we store the field followed by constants
[out]inv_orderIs set to 1 if the predicate is of the form 'const op field'
Return values:
0func_item is a simple predicate: a field is compared with constants
1Otherwise
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Defines