For one of our clients we make shop with more than 25 000 products. First year it was kindly smooth on standard hosting. But in past 2 monts it overload server and gives error messages of site load.
We moved shop on custom virtual server with 1GB RAM. After I saw a mysql slow query logs I realized that it is matter of some heavy queries that CS-Cart user to display maybe products. I attached some of the logs below.
I turned off all that I think could cause this crazy cross-table select. Is this a architecture problem of CS-Cart that it can’t handle this amount of product?
Most of the larger slow running queries are a result of building a cache for 'filters' and/or 'product features'.
From a brief look at the queries, I'd guess many of these are coming from the Admin side (not customer) and that you have large number of rows set for display (140?).
The admin side won't cache the data because it expects that you will probably be modifying it.