Developers/Optimize performance/en

Use the category as first condition
The more products you have in your database, the more you have to think about how to construct your search queries and what you should avoid! Databases have restrictions and for some queries you can create no index that can speed up retrieving the result.

To get a good performance you should always try to minimize the number of rows that matches as fast as possible. One good possibility is search only for products in the current category. As the number of products per category is usually low, this quickly reduces the number of rows that has to be filtered afterwards. When using the catalog index manager, add the following search condition first:

$search->compare( '==', 'catalog.index.catalog.id', (int) )

The speed-up is depending on the number of products in your database but with 100 000 or more products, it can be several 100ms.

Avoid sorting if possible
In product lists for categories sorting is necessary to match the order of products intended the shop owner. That's not a problem because the number of products per category is in a reasonable range. But when searching for products globally, this can be a big performance hit if the first condition don't reduce the number of rows drastically.

If your visitors are allowed to search for all products that are within a user-defined price range for example, your result set can quickly include 50% of all the products in your database. This fact is not yet problematic but as soon as this result set has to be sorted, the pain begins. The difference can be a few milliseconds without sortation compared to several seconds when sorting the result.

Flaky performance in list search when using different domain values
The MySQL optimizer always tries to find the best index for the given query but sometimes it's choice is totally wrong (see also). There are also cases where the correct index is chosen but only a part of the index is used even if much more matching key parts are available. Such a problem can occur when searching for items associated to products.

To try yourself, create the performance test data set with

phing setupperf && phing testperf

and change the list of domains whose items should be fetched too in lib/mshoplib/tests/Perf/CatalogIndexPerf.php method testListByPos from

$result = $indexManager->searchItems( $search, array( 'price', 'text', 'media' ), $total );

to

$result = $indexManager->searchItems( $search, array( 'attribute', 'price', 'text', 'media' ), $total );

You might see a very different output for the time needed. In my case it nearly doubled due to the addtional list items that have to be fetched but also the time required for the SELECT ... FROM "mshop_product_list" ... tripled compared to the time when only "array( 'price', 'text', 'media' )" was used. The key problem is known and Peter Zaitsev wrote an article about this already in 2007 but the problem still exists (at least in MySQL 5.1). His solution to use the "USE INDEX (...)" hint works in principle but can't be used for the Arcavias queries as they are highly dynamic depending on the conditions that are used. The only workaround is currently to keep the number of domains in searchItems low.