Harsha Kodali Harsha Kodali - 2 months ago 7
MySQL Question

A Mysql Query from joomla is taking very long time

Sorry for this Question. I am not a Good Programmer

Everything Executed well with few menu items. but with 14K menu items. i stuck with this query. This is happening when i try to access

administrator/index.php?option=com_modules&view=module&layout=edit&id=97

Is there any way to alter the query ??

Query

SELECT a.id AS value,
a.title AS text,
a.alias,
a.level,
a.menutype,
a.type,
a.template_style_id,
a.checked_out
FROM gjb0e_menu AS a
LEFT JOIN `gjb0e_menu` AS b
ON a.lft > b.lft
AND a.rgt < b.rgt
WHERE a.published != -2
GROUP BY a.id,
a.title,
a.alias,
a.level,
a.menutype,
a.type,
a.template_style_id,
a.checked_out,
a.lft
ORDER BY a.lft ASC


Php code

require_once JPATH_ADMINISTRATOR . '/components/com_menus/helpers/menus.php';
$menuTypes = MenusHelper::getMenuLinks();





<ul class="treeselect">
<?php foreach ($menuTypes as &$type) : ?>
<?php if (count($type->links)) : ?>
<?php $prevlevel = 0; ?>
<li>
<div class="treeselect-item pull-left">
<label class="pull-left nav-header"><?php echo $type->title; ?></label></div>
<?php foreach ($type->links as $i => $link) : ?>
<?php
if ($prevlevel < $link->level)
{
echo '<ul class="treeselect-sub">';
} elseif ($prevlevel > $link->level)
{
echo str_repeat('</li></ul>', $prevlevel - $link->level);
} else {
echo '</li>';
}
$selected = 0;
if ($this->item->assignment == 0)
{
$selected = 1;
} elseif ($this->item->assignment < 0)
{
$selected = in_array(-$link->value, $this->item->assigned);
} elseif ($this->item->assignment > 0)
{
$selected = in_array($link->value, $this->item->assigned);
}
?>
<li>
<div class="treeselect-item pull-left">
<input type="checkbox" class="pull-left" name="jform[assigned][]" id="<?php echo $id . $link->value; ?>" value="<?php echo (int) $link->value; ?>"<?php echo $selected ? ' checked="checked"' : ''; ?> />
<label for="<?php echo $id . $link->value; ?>" class="pull-left"><?php echo $link->text; ?> <span class="small"><?php echo JText::sprintf('JGLOBAL_LIST_ALIAS', $this->escape($link->alias));?></span></label>
</div>
<?php

if (!isset($type->links[$i + 1]))
{
echo str_repeat('</li></ul>', $link->level);
}
$prevlevel = $link->level;
?>
<?php endforeach; ?>
</li>
<?php endif; ?>
<?php endforeach; ?>
</ul>





Here is the function which creates the Query.

public static function getMenuLinks($menuType = null, $parentId = 0, $mode = 0, $published = array(), $languages = array())
{
$db = JFactory::getDbo();
$query = $db->getQuery(true)
->select('a.id AS value, a.title AS text, a.alias, a.level, a.menutype, a.type, a.template_style_id, a.checked_out')
->from('#__menu AS a')
->join('LEFT', $db->quoteName('#__menu') . ' AS b ON a.lft > b.lft AND a.rgt < b.rgt');

// Filter by the type
if ($menuType)
{
$query->where('(a.menutype = ' . $db->quote($menuType) . ' OR a.parent_id = 0)');
}

if ($parentId)
{
if ($mode == 2)
{
// Prevent the parent and children from showing.
$query->join('LEFT', '#__menu AS p ON p.id = ' . (int) $parentId)
->where('(a.lft <= p.lft OR a.rgt >= p.rgt)');
}
}

if (!empty($languages))
{
if (is_array($languages))
{
$languages = '(' . implode(',', array_map(array($db, 'quote'), $languages)) . ')';
}

$query->where('a.language IN ' . $languages);
}

if (!empty($published))
{
if (is_array($published))
{
$published = '(' . implode(',', $published) . ')';
}

$query->where('a.published IN ' . $published);
}

$query->where('a.published != -2')
->group('a.id, a.title, a.alias, a.level, a.menutype, a.type, a.template_style_id, a.checked_out, a.lft')
->order('a.lft ASC');

// Get the options.
$db->setQuery($query);

try
{
$links = $db->loadObjectList();
}
catch (RuntimeException $e)
{
JError::raiseWarning(500, $e->getMessage());

return false;
}

if (empty($menuType))
{
// If the menutype is empty, group the items by menutype.
$query->clear()
->select('*')
->from('#__menu_types')
->where('menutype <> ' . $db->quote(''))
->order('title, menutype');
$db->setQuery($query);

try
{
$menuTypes = $db->loadObjectList();
}
catch (RuntimeException $e)
{
JError::raiseWarning(500, $e->getMessage());

return false;
}

// Create a reverse lookup and aggregate the links.
$rlu = array();

foreach ($menuTypes as &$type)
{
$rlu[$type->menutype] = & $type;
$type->links = array();
}

// Loop through the list of menu links.
foreach ($links as &$link)
{
if (isset($rlu[$link->menutype]))
{
$rlu[$link->menutype]->links[] = & $link;

// Cleanup garbage.
unset($link->menutype);
}
}

return $menuTypes;
}
else
{
return $links;
}

Answer

You can simply remove the join. I'm pretty sure the join has absolutely no effect in your query, and it will be a lot quicker. You can try to run the query directly in your database with and without the join, to verify that the result is the same.

I'm not sure what this join was intended to do, but i guess it is some remnant of some functionality the programmer was trying to achieve, but that was realized differently or dropped, leaving a useless join.

The structure of this kind of hierarchical table is like this to prevent these sort of join. You have a lft and a rgt value, and any item that is between the lft and the rgt is a sub-item. In addition you have a level-value, telling you how deep in the tree a value is. So to select all children of an item, you can do:

select * where lft>item.lft and rgt>item.rgt

To select only direct descendants of your item, do

select * where lft>item.lft and rgt>item.rgt and level=item.level+1

The structure in the database is a little hard to maintain, requiring special functions to update the data, but it is quick to search and extract data.

So, your getMenuLinks() - function should start like

public static function getMenuLinks($menuType = null, $parentId = 0,
  $mode = 0, $published = array(), $languages = array())
{
  $db = JFactory::getDbo();
  $query = $db->getQuery(true)
    ->select('a.id AS value, a.title AS text, a.alias, a.level,
      a.menutype, a.type, a.template_style_id, a.checked_out')
    ->from('#__menu AS a');

  // Filter by the type
  if ($menuType) 
  ...