Recently, we at the Cloud & VPS Hosting Team held an internal meetup together with the CS-Cart architecture team, dedicated to optimizing the technical operation of projects on CS-Cart / Multi-Vendor. We discussed the following topics:
- Study of database queries to optimize the performance of CS-Cart/Multi-Vendor projects, plus most common problems encountered by developers and troubleshooting;
- Optimization through changes to the code in case database queries isn’t feasible and the code refactoring is required.
In this article, we will talk about the first part of the meeting. My hosting team and I often deal with performance study and analysis done for the projects that use our hosting solution. We investigate the root causes of problems for free within the scope of hosting plans. We fix issues if the changes are not significant, or give recommendations on how to fix them with the help of developers. Therefore, I made a selection of the most common problems and tips on how to solve them. This article will be useful for both developers and add-on and theme developers.
Database unavailability or slow operation is the most common problem faced by project owners. But the root cause of the slow work of a project doesn’t lie in the database server, but in its structure/architecture and queries to the database that are written by the developers. It is the queries that need to be optimized.
Roman Ananev, Head of SRE/DevOps
Previously, I’ve already mentioned how to detect issues with pages in your store. This time lets’ focus now on the underlying causes behind them.
Examples 1 & 2: No index or index is broken
This is the most common problem. Developers forget to add indexes to their tables or write queries against the existing indexes. As a result, the query scans the full table instead of several values. For large stores with a lot of goods, this is a very dangerous flaw.
To avoid this flaw, check the queries before releasing changes to the production server using a simple statement – EXPLAIN. Find details in the MySQL article “Avoiding Full Table Scans“.
Note that the availability of indexes doesn’t always mean they work. When a function is used directly on an indexed column, the database’s optimizer won’t be able to use the index to optimize the search. Creating and indexing a generated column will allow MySQL to optimize the search. By default, the database cannot use indexes on columns used as function parameters like FIND_IN_SET, MONTHNAME, etc. The virtual columns can help here, but you must be very careful with them.
Roman Ananev
Example 3: Multiple issues
Some requests may contain several problems at once.
The slide above shows a number of issues, such as:
- Including SQL_CALC_FOUND_ROWS statements tend to slow down queries significantly as it doesn’t scale well.
- The database will not use a sorting index (if exists) in cases where the query mixes ASC (the default if not specified) and DESC order.
- OFFSET (LIMIT x, y) clauses can be very slow when used with high offsets (e.g. with high page numbers when implementing paging).
Example 4: Harmful search
Now you realize why you should avoid complex and unoptimized queries, especially when it comes to product search. Product searches can be challenging in CS-Cart / Multi-Vendor if you have a large inventory.
While much can be said about search, I don’t wish to speak at length. Instead, I would give just one piece of advice:
- Avoid LIKE searches with leading wildcard. The database will not use an index when using like searches with a leading wildcard (e.g. ‘%123%’).
Someone might say that without a % sign, a fast search is impossible. I would say it’s not true: you can apply Elasticsearch and Sphinx Search for these purposes. Integration with these tools will speed up the search in large-inventory stores many times.
Our hosting has a partnership with Searchanise that allows you to save 40% on monthly plans.
Example 5: “0.0002 sec”, is it fast or slow?
Here is one more request deserving attention for such shortcomings as: “Selecting unnecessary columns”, “Joining or filtering using columns of different types in the same condition” and “Using non-numeric column types for numeric values”.
In this case, we were able to make very small optimizations of the code and the database structure, but we won more than two seconds on 10,000 requests, and there can be thousands of similar requests in a project.
24/7 monitoring
Our hosting automatically tracks the performance of projects and databases for every customer 24/7. When problems are found, we notify customers and recommend solutions.
To find performance issues, you can also contact us. We will investigate the problem and suggest an optimal solution. Our team has extensive experience in working with large and high-loaded projects.