OpenSEF RC5 SP2 Perfomance Improvement

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.