Don’t overlook the possibility of just plain bad SQL coding, especially if you’ve customized anything. Zen Cart’s SQL through 1.3.x is pretty darned inefficient with its SQL anyway (they’re fixing much of that in 1.4.x, currently in beta — around 700 queries removed in the generation of the Home Page alone!). When customizing ad-hoc SQL queries pieced together from variables and named constants and string constants and expressions and the like, a typo can easily be overlooked.
We just had this problem with constantly getting “2008 MySQL client ran out of memory” errors on the products listings pages (“?main_page=index…”). After reading this thread and worrying about indices and MySQL and Apache config. parameters and our shared hosting service not being able to handle the load, etc., I finally simply pasted the query as shown on the error page into a phpMySQLadmin SQL window and executed it.
It returned well over seven million records, from one of the smaller categories! This, mind you, for a store with only about 1,600 items, only about 20 of which were in that category!
In analyzing the, I found some embarrassing typos on my part: in my customized “{store root}/includes/index_filters/default.filter.php” file, I had used the same alias on both sides of the “=” in my custom “LEFT JOIN … ON … =”s! I had, for instance (bad table alias capitalized here to call attention to it, but not of course capitalized in the original PHP code):
$listing_sql = "select … left join " . TABLE_PRODUCTS_MYTYPE_EXTRA . " x on p.products_id = x.products_id
left join " . TABLE_MY_FILTER . " f on x.myfilter_id = X.myfilter_id,"
```when it *should*’ve been```php
$listing_sql = "select … left join " . TABLE_PRODUCTS_MYTYPE_EXTRA . " x on p.products_id = x.products_id
left join " . TABLE_MY_FILTER . " f on x.myfilter_id = F.myfilter_id,"
``` in all four times that that query appears (in various variants) in default_filter.php! Since I had more than one such custom filter, and all had that same typo (gah!), well, you can imagine why it returned so many records!
Fixing that brought the results down to a far more manageable mere several thousand, which no longer crashed, but which still returned *way* too many records and was showing duplicate products in the listing — better than an error message, perhaps, but still not good.
This time, the fault seemed to be in part of the original SQL query of the default_filter.php file! Maybe my custom product types and filters showed it up, but it seems to have been bad code all along, even in the default!
It has to do with the Manufacturers portion of the query (so if your store doesn’t use Manufacturers, this probably wouldn’t affect you, but you should still consider fixing it in case you do start using Manufacturers in the future).
Again, look for where the variable “$listing_sql” is assigned (it’s assigned four times, one of which will be executed based on various conditionals). In all four places, the table whose name is stored in the constant “TABLE_MANUFACTURERS” with alias “m” is listed as a cross join rather than a left or inner join! In all but the last one, this is mitigated by adding this conditional in the WHERE clause: “…and p.manufacturers_id = m.manufacturers.id” and so shouldn’t actually cause that problem unless the last one is executed.
It’s referenced right after “TABLE_PRODUCTS_DESCRIPTION” which is also listed as a cross join, but that one with good reason: the “languages_id” field of the “zen_products_description” table means that that is a many-to-one relation with the “zen_products” table and so needs to be filtered separately for current language.
I went ahead and reworded the query to make the TABLE_MANUFACTURERS referenced as a “LEFT JOIN ON p.manufacturers_id = m.manufacturers.id” in all four variants of the query, and removed the now-redundant “…and p.manufacturers_id = m.manufacturers.id” from the WHERE clause. To make the syntax cleaner, I moved it above the TABLE_PRODUCTS_DESCRIPTION reference.
Now my products index listing pages return no out-of-memory errors, and return just what they’re supposed to! And, they’re quite a bit faster, to boot!
