maria-developers team mailing list archive

[worklog-noreply@xxxxxxxxxxxx]

-----------------------------------------------------------------------
                              WORKLOG TASK
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
TASK...........: subquery optimizations: Use cache for correlated non-semijoin
		subqueries
CREATION DATE..: Mon, 11 May 2009, 19:03
SUPERVISOR.....: Monty
IMPLEMENTOR....: 
COPIES TO......: 
CATEGORY.......: Server-RawIdeaBin
TASK ID........: 18 (http://askmonty.org/worklog/?tid=18)
VERSION........: Server-9.x
STATUS.........: Cancelled
PRIORITY.......: 60
WORKED HOURS...: 0
ESTIMATE.......: 0 (hours remain)
ORIG. ESTIMATE.: 0

PROGRESS NOTES:

-=-=(Guest - Mon, 11 Jan 2010, 13:21)=-=-
Status updated.
--- /tmp/wklog.18.old.24677     2010-01-11 13:21:24.000000000 +0200
+++ /tmp/wklog.18.new.24677     2010-01-11 13:21:24.000000000 +0200
@@ -1 +1 @@
-Un-Assigned
+Cancelled

-=-=(Psergey - Mon, 11 May 2009, 19:05)=-=-
High-Level Specification modified.
--- /tmp/wklog.18.old.1385      2009-05-11 19:05:55.000000000 +0300
+++ /tmp/wklog.18.new.1385      2009-05-11 19:05:55.000000000 +0300
@@ -1 +1,12 @@
+Not a spec but some considerations:
 
+* eq_ref access has one-element "lookup cache"
+
+* Materialization optimization (WL#1110) also has lookup cache, see
+subselect_hash_semi_join_engine::test_if_l_operand_changed.
+
+The tricky parts are:
+
+1. Assemble a list of correlation references
+
+2. Determine cache size.



DESCRIPTION:

Suppose there is a subquery that is correlated, and is not in the WHERE clause
e.g. 

SELECT ..., (SELECT ... FROM t2 WHERE t2.col=t1.col) FROM t1; 

or 

SELECT ..., left_expr IN (SELECT ... FROM t2 WHERE t2.col=t1.col) FROM t1; 

In this case, the only strategy is to re-evaluate the predicate for every record
combination of the outer select.  This can be improved if we catch all
correlation references and implement a lookup cache:

  (left_expr, outer_ref_value1, ... outer_ref_valueN) -> predicate_value

it's hard to predict what will be the optimal size of the cache but one-element
cache will most certainly be worth it.


HIGH-LEVEL SPECIFICATION:



Not a spec but some considerations:

* eq_ref access has one-element "lookup cache"

* Materialization optimization (WL#1110) also has lookup cache, see
subselect_hash_semi_join_engine::test_if_l_operand_changed.

The tricky parts are:

1. Assemble a list of correlation references

2. Determine cache size.


ESTIMATED WORK TIME

ESTIMATED COMPLETION DATE
-----------------------------------------------------------------------
WorkLog (v3.5.9)