maria-developers team mailing list archive

[worklog-noreply@xxxxxxxxxxxx]

-----------------------------------------------------------------------
                              WORKLOG TASK
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
TASK...........: Subquery optimization: Avoid recalculating subquery if external fields
		values found in subquery cache
CREATION DATE..: Wed, 25 Nov 2009, 22:25
SUPERVISOR.....: Monty
IMPLEMENTOR....: Sanja
COPIES TO......: 
CATEGORY.......: Server-BackLog
TASK ID........: 66 (http://askmonty.org/worklog/?tid=66)
VERSION........: Server-5.2
STATUS.........: Assigned
PRIORITY.......: 60
WORKED HOURS...: 0
ESTIMATE.......: 0 (hours remain)
ORIG. ESTIMATE.: 0

PROGRESS NOTES:

-=-=(Psergey - Mon, 11 Jan 2010, 13:25)=-=-
As of today, there is code that
- collects outside references 
- creates a temporary table with index that would allow for fast lookups.

there is no code to
- fill the temporary table 
- make lookups into it

Reported zero hours worked. Estimate unchanged.

-=-=(Sanja - Fri, 11 Dec 2009, 15:09)=-=-
High-Level Specification modified.
--- /tmp/wklog.66.old.28164     2009-12-11 15:09:15.000000000 +0200
+++ /tmp/wklog.66.new.28164     2009-12-11 15:09:15.000000000 +0200
@@ -3,4 +3,4 @@
 
 
 To check/discuss:
-Are there sens to put subquery cache on all levels of subqueries of on highest.
+ To put subquery cache on all levels of subqueries or on highest level only.

-=-=(Sanja - Fri, 11 Dec 2009, 15:08)=-=-
Low Level Design modified.
--- /tmp/wklog.66.old.28072     2009-12-11 15:08:16.000000000 +0200
+++ /tmp/wklog.66.new.28072     2009-12-11 15:08:16.000000000 +0200
@@ -1 +1,6 @@
+All items on which subquery depend could be collected in
+st_select_lex::mark_as_dependent (direct of indirect reference?)
+
+Temporary table index should be created by all fields except result field
+(TMP_TABLE_PARAM::keyinfo).
 

-=-=(Sanja - Fri, 11 Dec 2009, 15:05)=-=-
High-Level Specification modified.
--- /tmp/wklog.66.old.27795     2009-12-11 15:05:04.000000000 +0200
+++ /tmp/wklog.66.new.27795     2009-12-11 15:05:04.000000000 +0200
@@ -1 +1,6 @@
+Attach subquery cache to each Item_subquery. Interface should allow to use hash
+or temporary table inside.
 
+
+To check/discuss:
+Are there sens to put subquery cache on all levels of subqueries of on highest.



DESCRIPTION:

Collect all outer items/references (left part of the subquiery and outer
references inside the subquery) in key string. Compare the string (which
represents certain value set of the references) against values in hash table and
return cached result of subquery if the reference values combination has already
been used.

For example in the following subquery:
(L1, L2) IN (SELECT A, B FROM T WHERE T.F1>OTER_FIELD)
set of references to look into the subquery cache is (L1, L2, OTER_FIELD).

The subquery cache should be implemented as simple LRU connected to the subquery.

Size of the subquery cache (in number of results (but maybe in used memory
amount)) is limited by session variable (query parameter?).


HIGH-LEVEL SPECIFICATION:



Attach subquery cache to each Item_subquery. Interface should allow to use hash
or temporary table inside.


To check/discuss:
 To put subquery cache on all levels of subqueries or on highest level only.


LOW-LEVEL DESIGN:



All items on which subquery depend could be collected in
st_select_lex::mark_as_dependent (direct of indirect reference?)

Temporary table index should be created by all fields except result field
(TMP_TABLE_PARAM::keyinfo).


ESTIMATED WORK TIME

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