Slow search in OpenCart? See this.

OpenCart is a very popular free open-source ecommerce solution. One of its best features is the amount of presented information with respect to product data. There are dozens of product data fields which can be used in a variety of combinations to present and sell your merchandise - various product description fields, assignment to categories, stores, related products, attributes, options, PayPal payment profiles, discounts, special prices, etc. Such a rich set of features has its drawbacks and perhaps one of the biggest downsides is the impact on the search performance on the larger stores.

This article will examine the prime affectors of the search speed in OpenCart and will do performance comparisons between the standard OpenCart search engine and the search extension developed by iSenseLabs - iSearchCorporate. Without further ado, let’s jump in.

A brief glimpse under the hood

OpenCart goes through the following steps when you search for products:

1. A database query is performed on a number of product tables and compares their fields with the search criteria. This step uses MySQL table joins and simple pattern matching via the LIKE operator. Both of these steps have an impact on the overall speed, however they are necessary if we want to return relevant results.

2. The query in Step 1 returns only Product ID’s. For each of these Product ID’s a separate query is made which fetches the full product information, using subqueries and joins.

3. This step does the same thing as Step 1, but it returns the total count of matched products. This is used to construct the pagination of your search results.

4. We iterate through each returned product in Step 2, calculate their prices and construct the URL’s. If your OpenCart has SEO URL’s enabled and a lot of products, this will lead to a lot of entries in the url_alias table, which will lead to a tangible performance impact for your whole OpenCart store.

All of the above steps are necessary in order for your customers to enjoy a feature-rich experience while visiting your web store. But is it possible to speed up this whole process and fetch relevant product data at the same time?

Have no fear. iSearchCorporate is here

iSearchCorporate is an OpenCart extension, developed by iSenseLabs. It is an extended version of one of our best-selling modules, called iSearch. It deals with all of the above issues by pre-caching the relevant product data in a single well-indexed table, which significantly reduces the time to find the needed information.

We will do a comparison between the search engines of OpenCart and iSearchCorporate to  the difference. The data is collected from a test environment with the following parameters:

• CPU: Intel(R) Core(TM) i7-870 @ 2.93 GHz

• RAM: 8 GB

• OS: Windows 7 Professional 64-bit

• Web Server: Apache 2.2.22

• Database Server: MySQL 5.5.24

• PHP Version: 5.4.3

• OpenCart:

  • Version 1.5.6.1

  • Installed vQmod

  • SEO URL’s enabled

  • Each product contains an SEO entry

The data was gathered cumulatively by generating 1,000,000 products with random product data. Each product contains Meta Descriptions, Meta Keywords, Description, Tags, SKU, UPC, EAN, JAN, ISBN, MPN, Location, SEO Keyword, Image, Manufacturer, Categories, Attributes and Options. A measurement of the server response time is made on each 1,000 products. Here are the results:

The horizontal axis displays the number of products, over which we perform the search, while the vertical axis displays the number of seconds it takes for the server to return the results. The blue line represents the results of OpenCart and the red line represents the results of iSearchCorporate. We see that OpenCart searches through the first 1,000 products for 0.4 seconds, compared to 0.33 for iSearchCorporate. Increasing the number of products leads to an increased response time. At the 1,000,000 mark OpenCart returns the results for 28.99 seconds, while iSearchCorporate handles the task in the mind-blowing 0.94 seconds.

How does this happen? iSearchCorporate pre-caches all of the relevant product data in a single table, which alleviates the need for table JOINs. It also skips the second query which fetches the full product data, since it is already stored in the cached table. An extra query for the total matched products count also becomes redundant. We also pre-cache the SEO URL’s for the products in order to avoid looking into the large url_alias table.

The only drawback of this approach is that the table with the pre-cached data becomes relatively large. For web stores with approximately 1,000,000 products the size of the iSearchCorporate table is approximately 1.5 GB. This is more than a reasonable price to pay, considering the storage amount, which most of the hosting companies have to offer.

Further measurements

It is worth mentioning that the iSearchCorporate search engine can be configured to look into the product data in different fashions, as well as to customize the way the search results are prioritized and ordered. We will take a look at how these features affect the performance of iSearchCorporate.

Search methods

iSearchCorporate incorporates 4 different search methods, each with their own advantages and disadvantages:

• FULLTEXT: This search method uses FULLTEXT indexing to look through the search data. This method is considered one of the fastest available. However, it has a few drawbacks - it can look only through words between 4 and 84 characters (default settings, which can be configured) and it returns an empty result if the search term matches one of the following stop words: http://dev.mysql.com/doc/refman/5.5/en/fulltext-stopwords.html It also does not work very well with non-alphabetical characters.

• LIKE “word%”: This method uses the LIKE operator to match words, which begin with your search term. This is the fastest method, because it is optimized to use the MySQL indexes. However, it does not match words, which have the search term in the middle of the word.

• LIKE “%word%”: This method uses the LIKE operator to match words, which contain the search term anywhere in the word. It can be safely considered the most accurate. On the other hand, it does not support MySQL indexes which may have a tangible performance impact.

• FULLTEXT and LIKE “%word%”: This method is a combination between the FULLTEXT and the LIKE “%word%” methods. It applies FULLTEXT for search terms that have alpha-numeric characters and are between 4 and 84 characters long, while applying LIKE “%word%” for the rest of the search terms. The result is a good compromise between both methods.

Here is a graphic, which displays the search speed of each of the above methods:

The horizontal axis displays the number of products and the vertical axis displays the number of seconds. We see that all 4 methods are searching relatively fast with a few remarks. The FULLTEXT search has an average of 0.3 seconds search speed, but starts becoming a bit unstable when it approaches the 1,000,000 product mark. The LIKE “%word%” search is the slowest one, as we predicted, and by the end it takes about 2.3 seconds to retrieve the results. The FULLTEXT + LIKE “%word%” method returns the results a bit faster and the LIKE “word%” method trumps them all by keeping an average of 0.027 seconds.

Prioritizing the results

iSearchCorporate supports custom weights for the search results, which order them with respect to the product field where the search term is matched. This feature will come in handy if you need to show at the top the products that have a matching SKU number. This sounds like a complicated logic and some might expect that it will affect the search speed. The following graphic uses the above 4 search methods, along with custom weights for the search results. Let us see what the statistics have to say.

We see that the search speed in iSearchCorporate is primarily affected by the search method and not by the number of custom weights, which are applied.

Summary

We see that iSearchCorporate can be safely considered one of the best search extensions on the market. It is fully compatible with OpenCart’s product fields, while at the same time it overcomes the inherent limitations of OpenCart’s architecture. If you wish to get more information, we welcome you to visit our new OpenCart 3.x release and the iSearchCorporate’s description pages:

iSearchCorporate in the OpenCart extension store

iSearchCorporate in the iSenseLabs website