Why OpenCart SEO URL is slowing down your page speed ... and how to FIX it

SEO URL is a must have thing for any (OpenCart) store who plan to grow.
Like most of e-commerce frameworks, OpenCart offer support for SEO URL and it's really simple to enable it.

From admin side, choose System > Settings > find your store name > click edit.
In store settings page, select tab "Server" and search option "Use SEO URLs?". Set that option to YES and now your store can take advantage of SEO Urls.
Of course, on your host you also need then .htaccess file.

Once SEO enabled, your dev team will discover a huge amount of requests to MySQL, in special, requests for table "url_alias".
Number of requests depends on how many categories you have in menu, how many products are in current page etc. In just few words is related to number of links you have in your page.

How to find how many requests are made in your case?

An easy way to find this answer is to list/print all requests sent to MySql and to count, using find tool from your browser, how many times "url_alias" can be found in text.

In system/library/db/mysqli.php find line "public function query($sql) {" and after that line add:
echo "SQL = " . $sql . "<br />";

Save file.
In your browser, open home page or any page you want and will see a long list of requests sent to MySQL.

Here's an example:

How you can see in image above, this store we checked was sending 1000+ requests which created an delay on page speed.
(1000+ = browser find tool wasn't counting over 1000)

Important: number of requests can be different in your case (50 or 3000 or etc) - depending on how many links (categories, products etc) you have in page.

If for example, each query takes 0.0015 seconds to process then all 1000 requests will delay page with 1.5 second;

Important: this delay is generated only to get SEO Links. Are excluded times to get products info or categories.

It's possible to solve this problem?

Short answer: YES.
Even better, it's possible to reduce all that 1000+ requests from our example store to a SINGLE REQUEST sent to your MySQL server.

Solution:

Some programming knowledge are required understand this solution. If for you, PHP, SQL etc it's a "foreign language" ask your dev team to read/implement this fix.
Getting each time info about a single keyword in 99% of cases will consume same execution time.

Example:
- Searching for a single keyword "rent-renault-clio-16-diesel" in table url_alias takes 0.0004 seconds

- Getting all info for keywords at once consume same time 0.0004 seconds  (3750 records)

Now, because we saw this thing, we can do a "magic trick".
In SEO URL controller (catalog/controller/startup/seo_url.php), will do a single request to MySQL and get all "url_alias" records.

Using an array will keep relation between query and keyword like this:

$url_alias = array('product_id=85' => 'rent-renault-clio-16-diesel', ...... 'category_id=245' => 'some-category-keyword');

Code example:

$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "url_alias");
// OR language based in case you use multilanguage keywords:
// $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "url_alias WHERE language_id = '" . (int)$this->config->get('config_language_id') . "'");

......

// build query => keyword relation (see example above)
foreach ($rows as $row) {
    $url_alias[$row['query']] = $row['keyword'];
}

Later (other areas of controller), each time you'll need keyword to build URL in controller you can get it by using $url_alias['your_key'], which will NOT make again request to MySQL (will use relation query => keyword we store in array);

.............................................

} elseif ($key == 'path') {
     $categories = explode('_', $value);

      foreach ($categories as $category) {

          $query = 'category_id=' . (int)$category;
          $keyword = isset($url_alias[$query]) ? $url_alias[$query] : false;
         
          if ($keyword) {
              $url .= '/' . $keyword;
           } else {
               $url = '';

               break;
           }
       }

.....................................

Conclusion:

In store described in this blog post, we reduced page loading time with 1.5 seconds just improving the way SEO links are generated.

If you are not familiar with PHP, MySQL etc and you think you have this problem in your store please contact us and we will help you.