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:

thd	thread handler
tables	list of leaves of join table tree
all_fields	All fields to be returned
conds	WHERE 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:

0	no errors
1	if all items were resolved
HA_ERR_KEY_NOT_FOUND	on 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_item	Predicate item
[out]	args	Here we store the field followed by constants
[out]	inv_order	Is set to 1 if the predicate is of the form 'const op field'

Return values:

0	func_item is a simple predicate: a field is compared with constants
1	Otherwise