A) Student Example:

ER – Model Example Project

Tables in Database:

  • wp_mp_organisation
  • wp_mp_student
  • wp_mp_studies

 See WP All-in-One Backupfile for Step A: see mail 

B: Datatable Content

=> Example Datatable 37 (Students)

Show Students with Studies as Subtable

Examples for Search: User 4295 => Me

Subtable Search Needed: (Think about it)

 

 

 

C: Query Editor:

 

Query Editor:

  1. Cache Querys?
  2. Subtable Searchable? => Could the LIMIT + OFFSET be done in the inner Query?
  3. Preview
  4. Reset Cache
  5. Is Temporary Table Approach used for join Subtable

D: Example SQL

Cached Queries see in table: wp_wr_query columns: cached_query, cached_count_query and temporary_tables

 

Query “normal approach”

"Normal" Cached:
#Count Query
SELECT vm.*,  
 GROUP_CONCAT(DISTINCT IFNULL(CONCAT('v1397::', sub_query_180_181.v1397),''), '||', IFNULL(CONCAT('v1398::', sub_query_180_181.v1398),''), '||', IFNULL(CONCAT('v1399::', sub_query_180_181.v1399),''), '||', IFNULL(CONCAT('v1400::', sub_query_180_181.v1400),''), '||', IFNULL(CONCAT('form_37_id::', sub_query_180_181.form_37_id),''), '||', IFNULL(CONCAT('token::', sub_query_180_181.token),'')  SEPARATOR '&&') as sub_query_180_181 
 
, GROUP_CONCAT(IFNULL(v1397,''), IFNULL(v1398,''), IFNULL(v1399,''), IFNULL(v1400,'')) as search_field_sub_query_180_181 
 FROM (SELECT
wp_mp_student.id as v1394,
wp_mp_student.firstname as v1395,
wp_mp_student.lastname as v1396,
form_36_id,
md5(concat('test', wp_mp_student.id) ) as token
FROM 
wp_mp_student##FILTERED_BY_LINK##
WHERE ##FILTERED_BY_LINK_WHERE##
GROUP BY v1394
HAVING ##FRONTEND_FILTER####LIMIT##) vm 
LEFT JOIN wp_wr_link sub_link_180_181_l1 ON  (sub_link_180_181_l1.entity1 = 42 AND sub_link_180_181_l1.id1 =  vm.v1394 AND sub_link_180_181_l1.entity2 = 43) 
LEFT JOIN wp_wr_link sub_link_180_181_l2 ON  (sub_link_180_181_l2.entity2 = 42 AND sub_link_180_181_l2.id2 =  vm.v1394 AND sub_link_180_181_l2.entity1 = 43) 
LEFT JOIN (
 SELECT
wp_mp_studies.id as v1397,
	wp_mp_studies.name as v1398,
	wp_mp_studies.skz1 as v1399,
	wp_mp_studies.skz2 as v1400,
	form_37_id,
	md5(concat('test', wp_mp_studies.id) ) as token
FROM 
wp_mp_studies
	GROUP BY v1397
  ) sub_query_180_181 ON 
 (sub_link_180_181_l1.entity1 = 42 AND sub_link_180_181_l1.id1 = vm.v1394 AND sub_link_180_181_l1.id2 = sub_query_180_181.v1397 ) OR 
 (sub_link_180_181_l2.entity2 = 42 AND sub_link_180_181_l2.id2 = vm.v1394 AND sub_link_180_181_l2.id1 = sub_query_180_181.v1397 ) 
 
 GROUP BY vm.v1394
   HAVING (( 1=##skipsearch## ) or (CONCAT(ifnull(v1394,''), ifnull(v1395,''), ifnull(v1396,''), search_field_sub_query_180_181) rlike '##search_value##')) 


#Query
SELECT vm.*,  
 GROUP_CONCAT(DISTINCT IFNULL(CONCAT('v1397::', sub_query_180_181.v1397),''), '||', IFNULL(CONCAT('v1398::', sub_query_180_181.v1398),''), '||', IFNULL(CONCAT('v1399::', sub_query_180_181.v1399),''), '||', IFNULL(CONCAT('v1400::', sub_query_180_181.v1400),''), '||', IFNULL(CONCAT('form_37_id::', sub_query_180_181.form_37_id),''), '||', IFNULL(CONCAT('token::', sub_query_180_181.token),'')  SEPARATOR '&&') as sub_query_180_181 
 
, GROUP_CONCAT(IFNULL(v1397,''), IFNULL(v1398,''), IFNULL(v1399,''), IFNULL(v1400,'')) as search_field_sub_query_180_181 
 FROM (SELECT
wp_mp_student.id as v1394,
wp_mp_student.firstname as v1395,
wp_mp_student.lastname as v1396,
form_36_id,
md5(concat('test', wp_mp_student.id) ) as token
FROM 
wp_mp_student##FILTERED_BY_LINK##
WHERE ##FILTERED_BY_LINK_WHERE##
GROUP BY v1394
HAVING ##FRONTEND_FILTER####LIMIT##) vm 
LEFT JOIN wp_wr_link sub_link_180_181_l1 ON  (sub_link_180_181_l1.entity1 = 42 AND sub_link_180_181_l1.id1 =  vm.v1394 AND sub_link_180_181_l1.entity2 = 43) 
LEFT JOIN wp_wr_link sub_link_180_181_l2 ON  (sub_link_180_181_l2.entity2 = 42 AND sub_link_180_181_l2.id2 =  vm.v1394 AND sub_link_180_181_l2.entity1 = 43) 
LEFT JOIN (
 SELECT
wp_mp_studies.id as v1397,
	wp_mp_studies.name as v1398,
	wp_mp_studies.skz1 as v1399,
	wp_mp_studies.skz2 as v1400,
	form_37_id,
	md5(concat('test', wp_mp_studies.id) ) as token
FROM 
wp_mp_studies
	GROUP BY v1397
  ) sub_query_180_181 ON 
 (sub_link_180_181_l1.entity1 = 42 AND sub_link_180_181_l1.id1 = vm.v1394 AND sub_link_180_181_l1.id2 = sub_query_180_181.v1397 ) OR 
 (sub_link_180_181_l2.entity2 = 42 AND sub_link_180_181_l2.id2 = vm.v1394 AND sub_link_180_181_l2.id1 = sub_query_180_181.v1397 ) 
 
 GROUP BY vm.v1394
   HAVING (( 1=##skipsearch## ) or (CONCAT(ifnull(v1394,''), ifnull(v1395,''), ifnull(v1396,''), search_field_sub_query_180_181) rlike '##search_value##')) 
"Normal" Executed:
#Count Query
SELECT COUNT(*) FROM (SELECT vm.*,  
 GROUP_CONCAT(DISTINCT IFNULL(CONCAT('v1397::', sub_query_180_181.v1397),''), '||', IFNULL(CONCAT('v1398::', sub_query_180_181.v1398),''), '||', IFNULL(CONCAT('v1399::', sub_query_180_181.v1399),''), '||', IFNULL(CONCAT('v1400::', sub_query_180_181.v1400),''), '||', IFNULL(CONCAT('form_37_id::', sub_query_180_181.form_37_id),''), '||', IFNULL(CONCAT('token::', sub_query_180_181.token),'')  SEPARATOR '&&') as sub_query_180_181 
 
, GROUP_CONCAT(IFNULL(v1397,''), IFNULL(v1398,''), IFNULL(v1399,''), IFNULL(v1400,'')) as search_field_sub_query_180_181 
 FROM (SELECT
wp_mp_student.id as v1394,
wp_mp_student.firstname as v1395,
wp_mp_student.lastname as v1396,
form_36_id,
md5(concat('test', wp_mp_student.id) ) as token
FROM 
wp_mp_student

GROUP BY v1394
) vm 
LEFT JOIN wp_wr_link sub_link_180_181_l1 ON  (sub_link_180_181_l1.entity1 = 42 AND sub_link_180_181_l1.id1 =  vm.v1394 AND sub_link_180_181_l1.entity2 = 43) 
LEFT JOIN wp_wr_link sub_link_180_181_l2 ON  (sub_link_180_181_l2.entity2 = 42 AND sub_link_180_181_l2.id2 =  vm.v1394 AND sub_link_180_181_l2.entity1 = 43) 
LEFT JOIN (
 SELECT
wp_mp_studies.id as v1397,
	wp_mp_studies.name as v1398,
	wp_mp_studies.skz1 as v1399,
	wp_mp_studies.skz2 as v1400,
	form_37_id,
	md5(concat('test', wp_mp_studies.id) ) as token
FROM 
wp_mp_studies
	GROUP BY v1397
  ) sub_query_180_181 ON 
 (sub_link_180_181_l1.entity1 = 42 AND sub_link_180_181_l1.id1 = vm.v1394 AND sub_link_180_181_l1.id2 = sub_query_180_181.v1397 ) OR 
 (sub_link_180_181_l2.entity2 = 42 AND sub_link_180_181_l2.id2 = vm.v1394 AND sub_link_180_181_l2.id1 = sub_query_180_181.v1397 ) 
 
 GROUP BY vm.v1394
   HAVING (( 1=1 ) or (CONCAT(ifnull(v1394,''), ifnull(v1395,''), ifnull(v1396,''), search_field_sub_query_180_181) rlike '##search_value##')) 
) a;

#Query
SELECT * FROM (SELECT vm.*,  
 GROUP_CONCAT(DISTINCT IFNULL(CONCAT('v1397::', sub_query_180_181.v1397),''), '||', IFNULL(CONCAT('v1398::', sub_query_180_181.v1398),''), '||', IFNULL(CONCAT('v1399::', sub_query_180_181.v1399),''), '||', IFNULL(CONCAT('v1400::', sub_query_180_181.v1400),''), '||', IFNULL(CONCAT('form_37_id::', sub_query_180_181.form_37_id),''), '||', IFNULL(CONCAT('token::', sub_query_180_181.token),'')  SEPARATOR '&&') as sub_query_180_181 
 
, GROUP_CONCAT(IFNULL(v1397,''), IFNULL(v1398,''), IFNULL(v1399,''), IFNULL(v1400,'')) as search_field_sub_query_180_181 
 FROM (SELECT
wp_mp_student.id as v1394,
wp_mp_student.firstname as v1395,
wp_mp_student.lastname as v1396,
form_36_id,
md5(concat('test', wp_mp_student.id) ) as token
FROM 
wp_mp_student

GROUP BY v1394
LIMIT 10 OFFSET 0) vm 
LEFT JOIN wp_wr_link sub_link_180_181_l1 ON  (sub_link_180_181_l1.entity1 = 42 AND sub_link_180_181_l1.id1 =  vm.v1394 AND sub_link_180_181_l1.entity2 = 43) 
LEFT JOIN wp_wr_link sub_link_180_181_l2 ON  (sub_link_180_181_l2.entity2 = 42 AND sub_link_180_181_l2.id2 =  vm.v1394 AND sub_link_180_181_l2.entity1 = 43) 
LEFT JOIN (
 SELECT
wp_mp_studies.id as v1397,
	wp_mp_studies.name as v1398,
	wp_mp_studies.skz1 as v1399,
	wp_mp_studies.skz2 as v1400,
	form_37_id,
	md5(concat('test', wp_mp_studies.id) ) as token
FROM 
wp_mp_studies
	GROUP BY v1397
  ) sub_query_180_181 ON 
 (sub_link_180_181_l1.entity1 = 42 AND sub_link_180_181_l1.id1 = vm.v1394 AND sub_link_180_181_l1.id2 = sub_query_180_181.v1397 ) OR 
 (sub_link_180_181_l2.entity2 = 42 AND sub_link_180_181_l2.id2 = vm.v1394 AND sub_link_180_181_l2.id1 = sub_query_180_181.v1397 ) 
 
 GROUP BY vm.v1394
   HAVING (( 1=1 ) or (CONCAT(ifnull(v1394,''), ifnull(v1395,''), ifnull(v1396,''), search_field_sub_query_180_181) rlike '##search_value##')) 
) a ORDER BY v1396 ASC 

Query “temporary table approach”

"Temporary" Cached:
#Temporary Table
CREATE TEMPORARY TABLE IF NOT EXISTS sub_query_180_181 
 (INDEX sub_query_180_181_idx (v1397)) SELECT CASE WHEN sub_link_180_181_l1.id2 is null THEN sub_link_180_181_l2.id1 else sub_link_180_181_l1.id2 end as id1, 
wp_mp_studies.id as v1397,
	wp_mp_studies.name as v1398,
	wp_mp_studies.skz1 as v1399,
	wp_mp_studies.skz2 as v1400,
	form_37_id,
	md5(concat('test', wp_mp_studies.id) ) as token
FROM 
wp_mp_studies LEFT JOIN wp_wr_link sub_link_180_181_l1 ON  (sub_link_180_181_l1.entity1 = 43 AND sub_link_180_181_l1.id1 =  wp_mp_studies.id AND sub_link_180_181_l1.entity2 = 42) 
LEFT JOIN wp_wr_link sub_link_180_181_l2 ON  (sub_link_180_181_l2.entity2 = 43 AND sub_link_180_181_l2.id2 =  wp_mp_studies.id AND sub_link_180_181_l2.entity1 = 42) 
 WHERE  NOT (  sub_link_180_181_l1.entity1 is null and  sub_link_180_181_l2.entity1 is null) ; 

#Cached Count Query
SELECT vm.*,  
 GROUP_CONCAT(DISTINCT IFNULL(CONCAT('v1397::', sub_query_180_181.v1397),''), '||', IFNULL(CONCAT('v1398::', sub_query_180_181.v1398),''), '||', IFNULL(CONCAT('v1399::', sub_query_180_181.v1399),''), '||', IFNULL(CONCAT('v1400::', sub_query_180_181.v1400),''), '||', IFNULL(CONCAT('form_37_id::', sub_query_180_181.form_37_id),''), '||', IFNULL(CONCAT('token::', sub_query_180_181.token),'')  SEPARATOR '&&') as sub_query_180_181 
 
, GROUP_CONCAT(IFNULL(v1397,''), IFNULL(v1398,''), IFNULL(v1399,''), IFNULL(v1400,'')) as search_field_sub_query_180_181 
 FROM (SELECT
wp_mp_student.id as v1394,
wp_mp_student.firstname as v1395,
wp_mp_student.lastname as v1396,
form_36_id,
md5(concat('test', wp_mp_student.id) ) as token
FROM 
wp_mp_student##FILTERED_BY_LINK##
WHERE ##FILTERED_BY_LINK_WHERE##
GROUP BY v1394
HAVING ##FRONTEND_FILTER####LIMIT##) vm 

 LEFT JOIN  sub_query_180_181 ON vm.v1394 = sub_query_180_181.id1  
 GROUP BY vm.v1394
   HAVING (( 1=##skipsearch## ) or (CONCAT(ifnull(v1394,''), ifnull(v1395,''), ifnull(v1396,''), search_field_sub_query_180_181) rlike '##search_value##')) 


#Cached Query
SELECT vm.*,  
 GROUP_CONCAT(DISTINCT IFNULL(CONCAT('v1397::', sub_query_180_181.v1397),''), '||', IFNULL(CONCAT('v1398::', sub_query_180_181.v1398),''), '||', IFNULL(CONCAT('v1399::', sub_query_180_181.v1399),''), '||', IFNULL(CONCAT('v1400::', sub_query_180_181.v1400),''), '||', IFNULL(CONCAT('form_37_id::', sub_query_180_181.form_37_id),''), '||', IFNULL(CONCAT('token::', sub_query_180_181.token),'')  SEPARATOR '&&') as sub_query_180_181 
 
, GROUP_CONCAT(IFNULL(v1397,''), IFNULL(v1398,''), IFNULL(v1399,''), IFNULL(v1400,'')) as search_field_sub_query_180_181 
 FROM (SELECT
wp_mp_student.id as v1394,
wp_mp_student.firstname as v1395,
wp_mp_student.lastname as v1396,
form_36_id,
md5(concat('test', wp_mp_student.id) ) as token
FROM 
wp_mp_student##FILTERED_BY_LINK##
WHERE ##FILTERED_BY_LINK_WHERE##
GROUP BY v1394
HAVING ##FRONTEND_FILTER####LIMIT##) vm 

 LEFT JOIN  sub_query_180_181 ON vm.v1394 = sub_query_180_181.id1  
 GROUP BY vm.v1394
   HAVING (( 1=##skipsearch## ) or (CONCAT(ifnull(v1394,''), ifnull(v1395,''), ifnull(v1396,''), search_field_sub_query_180_181) rlike '##search_value##')) 


"Temporary" Executed:
#Temporary Table
 CREATE TEMPORARY TABLE IF NOT EXISTS sub_query_180_181 
 (INDEX sub_query_180_181_idx (v1397)) SELECT CASE WHEN sub_link_180_181_l1.id2 is null THEN sub_link_180_181_l2.id1 else sub_link_180_181_l1.id2 end as id1, 
wp_mp_studies.id as v1397,
	wp_mp_studies.name as v1398,
	wp_mp_studies.skz1 as v1399,
	wp_mp_studies.skz2 as v1400,
	form_37_id,
	md5(concat('test', wp_mp_studies.id) ) as token
FROM 
wp_mp_studies LEFT JOIN wp_wr_link sub_link_180_181_l1 ON  (sub_link_180_181_l1.entity1 = 43 AND sub_link_180_181_l1.id1 =  wp_mp_studies.id AND sub_link_180_181_l1.entity2 = 42) 
LEFT JOIN wp_wr_link sub_link_180_181_l2 ON  (sub_link_180_181_l2.entity2 = 43 AND sub_link_180_181_l2.id2 =  wp_mp_studies.id AND sub_link_180_181_l2.entity1 = 42) 
 WHERE  NOT (  sub_link_180_181_l1.entity1 is null and  sub_link_180_181_l2.entity1 is null) ; 

#COUNT QUERY

 SELECT COUNT(*) FROM (SELECT
wp_mp_student.id as v1394,
wp_mp_student.firstname as v1395,
wp_mp_student.lastname as v1396,
form_36_id,
md5(concat('test', wp_mp_student.id) ) as token
FROM 
wp_mp_student
WHERE  (( 1=1 ) or (CONCAT(ifnull(wp_mp_student.id,''), ifnull(wp_mp_student.firstname,''), ifnull(wp_mp_student.lastname,'')) rlike '##search_value##')) 
GROUP BY v1394
) a;

#QUERY
 SELECT * FROM (SELECT vm.*,  
 GROUP_CONCAT(DISTINCT IFNULL(CONCAT('v1397::', sub_query_180_181.v1397),''), '||', IFNULL(CONCAT('v1398::', sub_query_180_181.v1398),''), '||', IFNULL(CONCAT('v1399::', sub_query_180_181.v1399),''), '||', IFNULL(CONCAT('v1400::', sub_query_180_181.v1400),''), '||', IFNULL(CONCAT('form_37_id::', sub_query_180_181.form_37_id),''), '||', IFNULL(CONCAT('token::', sub_query_180_181.token),'')  SEPARATOR '&&') as sub_query_180_181 
 
, GROUP_CONCAT(IFNULL(v1397,''), IFNULL(v1398,''), IFNULL(v1399,''), IFNULL(v1400,'')) as search_field_sub_query_180_181 
 FROM (SELECT
wp_mp_student.id as v1394,
wp_mp_student.firstname as v1395,
wp_mp_student.lastname as v1396,
form_36_id,
md5(concat('test', wp_mp_student.id) ) as token
FROM 
wp_mp_student
WHERE  (( 1=1 ) or (CONCAT(ifnull(wp_mp_student.id,''), ifnull(wp_mp_student.firstname,''), ifnull(wp_mp_student.lastname,'')) rlike '##search_value##')) 
GROUP BY v1394
LIMIT 10 OFFSET 0) vm 

 LEFT JOIN  sub_query_180_181 ON vm.v1394 = sub_query_180_181.id1  
 GROUP BY vm.v1394) a ORDER BY v1396 ASC 

E: Results

Normal Approach

2,8 + 0,004s = ~2,8s

Temporary Table Approach

0.184 + 0,055 + 0,055 => ~0,2s

F: Parts in Source Code:

Plugin Path: volumes/wordpress/html/wp-content/plugins/wonderful_relations

SourceCode Parts:

Query Generation / Execution:
modules/queries/

DataTables:
modules/datatable/actions/GetEntries.php

=> get_entries

 

Logging: (show error_log output)

tail -f wp-content/debug.log