We have a strange situation.
Recently, the pages have been loading very slowly, sometimes taking up to 9 seconds. The same in the frontend or backend, especially in the product list.
The hosting people blame bots, the platform, memory/cpu overruns, although there has been no update for several months.
Using the &debug parameter, I found a SQL that takes a very long time. The SQL command is the one that returns the product list.
Our site is for online sales of toners and ink cartridges. The specific thing is that we have 1500 products so far but we have 4500 categories (printers) and the products_categories table has over 30000 lines.
I identified a join in the source code (app\functions\fn.products.php) that could be the cause:
$categories_join = db_quote(
' INNER JOIN ?:products_categories as products_categories ON products_categories.product_id = products.product_id' .
' INNER JOIN ?:categories ON ?:categories.category_id = products_categories.category_id' .
' ?p' .
' ?p',
$category_avail_cond,
$feature_search_condition
);
I believe that this can be improved to avoid generation of 45 millions lines to group and sort.