Hi,

finally, after 3 years of using zencart system I´ve got a problem that I´m not capable to deal with:

one of my sites have sometimes really very slow page load.

I have found that the problem is in my query times, here are some exemples:

Parse Time: 50.527 - Number of Queries: 117 - Query Time: 50.1647379854
Parse Time: 55.450 - Number of Queries: 105 - Query Time: 55.0771047457
Parse Time: 64.838 - Number of Queries: 80 - Query Time: 64.5425017912

these loads comes in some waves - everything goes well, then suddenly it stops (firefox writes "waiting for www....")

this site is hosted on VPS with 764 MB RAM along with other 4 zencart installation, none of them has more than 100 UV/day

What could be the problem?
How decrease these query times?

thank you very much for your help

For anybody to even start answering this we'd need confirmation that you're running the latest version of Zen Cart, and which add-ons you have installed.

I´m using version 1.3.8a, which is pretty moded

some of the most important modules are:

• image handler2
• reward points
• recover cart sales
• column grid layout
• google analytics

and a few minor mods

I seem to remember that there was a slow query in versions prior to 1.3.9. But it was in a little used area, which is why it had been there for a long time without anybody noticing.

None of the mods are known for slow queries, but they can come from anywhere and are more likely to occur in "minor mods" if they're not used by many people and so there's less feedback on their performance.

If an upgrade doesn't fix this for you, then to find the specific slow query you would need to check with your webhost that they have the mysql slow query function enabled (they should) and have then provide you with the details of those relating to your sites.

If you have control over the server, you can look at these directly. The exact location of the log can vary from server to server, but /var/log/mysql/mysql-slow.log is a good place to start on many Linux servers.

thanks for the idea, I will look into it and surely come back with and update (maybe someone will find it helpfull in the future)

Finally, I have found through slow log that problems make especially one specific query like this:

Query_time: 94 Lock_time: 0 Rows_sent: 7 Rows_examined: 25131748

select DISTINCT p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, p.products_price_sorter, p.products_qty_box_status FROM products p, products_description pd, products_to_categories p2c, products_attributes pa, categories cat , products_options po , products_options_values pov WHERE (p.products_status = 1 AND p.products_price >= 10 AND p.products_price <= 19.99 AND p.products_quantity > 0 AND p.products_id = pd.products_id AND cat.categories_id = p2c.categories_id AND p.products_id = p2c.products_id) limit 800, 10;

I have tried to uninstall all suspicious contributions and addons (like special alpha product sorter or sidebox for searching by attributes), but I just can´t resolve what to do with this query which appears also after uninstalling these addons...

I find especially number of rows examined too high, but can´t figure out why this happens :-(

Well that would certainly do it. It's one very poorly constructed query.

In particular it references the product_attributes table without joining it in any way (that could create an awful lot of duplicate rows in the result obtained without the benefit of any indexes), and although it include a join to categories table, it appears to have no need of that table.

That's the goodish news. The bad news is that core Zen Cart code is just way better than this, so I can't give you much advice as to where to find it.

You could try the developers tool kit in the admin to search for all instances of "select DISTINCT p.products_id, pd.products_name," on your site.

Or you could look to see what add-ons or custom code you're using that would be interested in comparing price ranges (only advanced search in the Zen Cart core code does that).

Hallelujah :clap:

it seems that problem is solved - I simply deleted one of the minor (yet important) addons - search by attributes.

Not sure if it was installed incorrectly or not, but after I deleted it (not only turn off, but physically deleted from the site), everything runs smoothly

thank you very much, kuroi, for your help, without it, I would never find the answer