SET join_cache_level=6; SHOW VARIABLES LIKE "%join_cache%"; Variable_name Value join_cache_level 6 DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; ****************** ** Join Test #1 ** ****************** ** Create Table t1 ** CREATE TABLE t1 ( project_id int(11) NOT NULL auto_increment, project_row_lock int(11) NOT NULL default '0', project_name varchar(80) NOT NULL default '', client_ptr int(11) NOT NULL default '0', project_contact_ptr int(11) default NULL, client_contact_ptr int(11) default NULL, billing_contact_ptr int(11) default NULL, comments mediumtext, PRIMARY KEY (project_id), UNIQUE KEY project (client_ptr,project_name) ) ENGINE=ndbcluster; ** Create Table t2 ** CREATE TABLE t2 ( period_id int(11) NOT NULL auto_increment, period_type enum('user_table','client_table', 'role_table','member_table', 'project_table') default NULL, period_key int(11) default NULL, start_date datetime default NULL, end_date datetime default NULL, work_load int(11) default NULL, PRIMARY KEY (period_id), KEY period_index (period_type,period_key), KEY date_index (start_date,end_date) ) ENGINE=ndbcluster; ** Create Table t4 ** CREATE TABLE t4 ( client_id int(11) NOT NULL auto_increment, client_row_lock int(11) NOT NULL default '0', client_name varchar(80) NOT NULL default '', contact_ptr int(11) default NULL, comments mediumtext, PRIMARY KEY (client_id), UNIQUE KEY client_name (client_name) ) ENGINE=ndbcluster; ** Insert Data ** DROP TABLE t1,t2,t4; ***************** ** End Test #1 ** ***************** ****************** ** Join Test #2 ** ** Left Joins ** ****************** ** Create Tables ** CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b))ENGINE=ndbcluster; CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b))ENGINE=ndbcluster; CREATE TABLE t7 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b))ENGINE=ndbcluster; CREATE TABLE t8 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b))ENGINE=ndbcluster; ** Insert Data ** ** Run selects ** SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b FROM t5 LEFT JOIN ( (t6, t7) LEFT JOIN t8 ON t7.b=t8.b AND t6.b < 10 ) ON t6.b >= 2 AND t5.b=t7.b AND (t8.a < 1 OR t8.c IS NULL); a b a b a b a b 1 1 1 2 1 1 NULL NULL 1 1 3 2 1 1 NULL NULL 2 2 1 2 2 2 0 2 2 2 3 2 2 2 0 2 3 3 NULL NULL NULL NULL NULL NULL DROP TABLE t5,t6,t7,t8;