categories-query-benchmark.php

<?php

// Origin
// -----------------------------------------------------------------------------------------
$rootCategory = $this->getRootCategory();
$db = $this->getDbo();

$start_time = microtime(TRUE);

foreach (range(1,500) as $r):

// Main query
$query = $db->getQuery(true)
	->select('cat.*')
	->from('#__categories AS cat')
	->leftJoin('#__categories AS cat2 ON cat2.`lft` BETWEEN cat.`lft` AND cat.`rgt`')
	->where('cat.`extension` = "com_organization"')
	->where('cat.`published` = 1')
	->group('cat.`id`')
	->order('cat.`lft`');

// Sub query get items count per categories.
$subQuery = $db->getQuery(true)
	->select('COUNT(id) AS item_count, catid')
	->from('#__organization_items')
	->where('published = 1')
	->group('catid');

// Join item count to main query
$query->select('SUM(IFNULL(item.item_count, "0")) AS item_count')
	->join('LEFT', '(' . $subQuery . ') AS item ON cat2.id = item.catid');

// Add root where id
$query->where('cat.`parent_id` = ' . (int) $rootCategory->id);

// Do query
$categories = $db->setQuery($query)->loadObjectList();

endforeach;

$end_time = microtime(TRUE);

echo $end_time - $start_time;die;



// New
// -----------------------------------------------------------------------------------------
$rootCategory = $this->getRootCategory();
$db = $this->getDbo();

$start_time = microtime(TRUE);

foreach (range(1,500) as $r):

// Main query
$query = $db->getQuery(true)
	->select('*')
	->from('#__categories')
	->where('`extension` = "com_organization"')
	->where('`published` = 1')
	->where('`parent_id` = ' . (int) $rootCategory->id)
	->group('`id`')
	->order('`lft`');

$categories = $db->setQuery($query)->loadObjectList();

// Sub query get items count per categories.
$subCatQuery = $db->getQuery(true)
	->select('COUNT(item.id) AS item_count, item.catid, cat.lft, cat.rgt')
	->join('LEFT', '#__categories AS cat ON item.catid = cat.id')
	->from('#__organization_items AS item')
	->where('item.published = 1')
	->group('cat.id');

$countItems = $db->setQuery($subCatQuery)->loadObjectList();

// Count sub categories
foreach ($countItems as $countItem)
{
	foreach ($categories as &$category)
	{
		if (!property_exists($category, 'item_count'))
		{
			$category->item_count = 0;
		}

		if ($category->lft >= $countItem->lft && $category->rgt <= $countItem->rgt)
		{
			$category->item_count += $countItem->item_count;

			continue;
		}
	}
}

endforeach;

$end_time = microtime(TRUE);

echo $end_time - $start_time;die;