Notes by JDeveloper for JDeveloper
Once you master it, you're gonna like it

Becareful with Subquery Select with more than 1 level deep

Written by Viktor Iwan

I recently found a heavy loading of a page in mysite which using iJoomla Component named Guru. After profiling with Zend Studio, i found out the real problem cause by 2 level subquery

i'm talking about queries like this:

$sql ="SELECT id FROM #__guru_task WHERE id IN (SELECT media_id FROM #__guru_mediarel WHERE type = 'dtask' AND type_id in (SELECT id FROM #__guru_days WHERE pid =".intval($id).") ) ";

It's 2 level of subquery with 'IN' statement...

To solve this kind of problem, you only have to change the IN statement into multiple queries, like this:

$sql ="SELECT id FROM #__guru_days WHERE pid =".$id;
$db->setQuery($sql);
$db->query();
$temp = $db->loadColumn();
		
$sql ="SELECT media_id FROM #__guru_mediarel WHERE type = 'dtask' AND type_id in (".implode(',',$temp).")";
$db->setQuery($sql);
$db->query();
$temp = $db->loadColumn();
		
$sql ="SELECT id FROM #__guru_task WHERE id IN (".implode(',',$temp).")";
$db->setQuery($sql);
$db->query();
$all_lessons = $db->loadColumn();

 

 After some optimization with this approach, i'm able to change this:

 

Into this:

 

Look how mysql perform for the same result... 25 seconds load into 0.8 seconds load... That Fast !!!

Moral of the story: Don't use subquery with 'IN' statement more than 1 level. You can search on google with keyword "MySQL Subquery Slow Performance" and you'll see more people suggest the same.