CustomPCSoftware.com

Hi, all!Recently I’ve rewritten a pretty large site on Joomla (it has about 200 online users all the time, as Joomla backend stats shows, >17000 content items etc.) and noticed that the page generation time started to grow ftom 0.101 secs to 0.5 after the first day, 0.7 - 1 next day…). Not very good, right? First of all I went to the MySQL console at the time of high loads and… voila - there is a `opensef_sef` MySQL query present almost constantly. Guys, can anybody explain me why do they fetch into the memory almost all records in sef.php [Line #173]:

if ($sefSite->id == null) $sefSite->id = -1;
$query = “SELECT * FROM #__opensef_sef WHERE published = ‘1′” .
“\nAND valid = ‘1′” .
“\nAND (direction IS NULL OR direction = ‘’ OR direction = ‘o’)” .
“\nAND (site_id IS NULL ” .
($sefSite->id ? ‘ OR site_id = ‘ . $sefSite->id : ‘’) . ‘)’ .
“\nORDER BY site_id DESC, published DESC, valid DESC, LENGTH(direction) DESC”;
$database->setQuery( $query );
if ($database->loadResult()) {
$opensef_aliases = $database->loadObjectList();
}

A quick fix is to replace the query with this one:

if ($sefSite->id == null) $sefSite->id = -1;
$query = “SELECT * FROM #__opensef_sef WHERE published = ‘1′” .
“\nAND valid = ‘1′” .
“\nAND (direction IS NULL OR direction = ‘’ OR direction = ‘o’)” .
“\nAND (site_id IS NULL ” .
($sefSite->id ? ‘ OR site_id = ‘ . $sefSite->id : ‘’) . ‘)’ .
“\nAND (external=’{$_SERVER[’REQUEST_URI’]}’” .
“\nORDER BY site_id DESC, published DESC, valid DESC, LENGTH(direction) DESC”;
$database->setQuery( $query );
if ($database->loadResult()) {
$opensef_aliases = $database->loadObjectList();
}

After that, find the lookupOutgoing function  in administrator/components/com_sef/sef.class.php and replace it with this one:

function lookupOutgoing( $site_id, $url ) { // static method
global $opensef_aliases, $database;

$returnaliases = array();
/*
if ($site_id == null) $site_id = -1;
if (count($opensef_aliases) > 0) {
foreach($opensef_aliases as $aliases){
if ( $aliases->internal == $url ) {
$returnaliases[] = $aliases;
}
}
}
*/
if (!count($returnaliases))
{
$query = “SELECT * FROM #__opensef_sef WHERE published = ‘1′” .
“\nAND valid = ‘1′” .
“\nAND (direction IS NULL OR direction = ” OR direction = ‘o’)” .
“\nAND internal=’$url’”.
“\nORDER BY site_id DESC, published DESC, valid DESC, LENGTH(direction) DESC limit 1″;

$database->setQuery( $query );
if ($database->loadResult())
{
$aliases = $database->loadObjectList();
foreach($aliases as $k=>$val)
{
$returnaliases[] = $val;
}
}
}

if (is_array( $returnaliases ) && count( $returnaliases )) {
$returnaliases = xclCastObjectList( $returnaliases, ‘JosOpenSEFAlias’ );
return $returnaliases[0];
}
$null = null;
return $null;
}

Hurray! It works as a charm. Page generation time is now 0.07 seconds.

Hope this will help somebody else.

Thanks.