opt_sum.cc File Reference

#include "sql_priv.h"
#include "key.h"
#include "sql_select.h"

Include dependency graph for opt_sum.cc:

Go to the source code of this file.

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)

Definition in file opt_sum.cc.

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

Definition at line 237 of file opt_sum.cc.

Here is the call graph for this function:

Here is the caller graph for this function:

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

Definition at line 527 of file opt_sum.cc.