If your oscommerce store is a particularly large one, and you are constantly adding new ranges of products, ading specials and lots of different products attributes, you will eventually encounter the 1104 MAX_JOIN_SIZE error on your product listing pages.
This is somethng I recently came across myself on a site with around 1500 individual products with dozens of associated product attribute variations
The error message appears n some or all of your product listng pages, and possibly on the product info pages themselves.
It looks something like this:
1104 - The SELECT would examine more rows than MAX_JOIN_SIZE.
Check your WHERE and use SET SQL_BIG_SELECTS=1 or
SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok
I'll cut to the chase and direct you straight to the fix that I uploaded to the oscommerce contributions section:
oscommerce 1104 MAX_JOIN_SIZE error fixThe contribution file includes a step by step guide on how to fix this error and links to various other resources that discuss the underlyng problem.
So what is the underlying problem?
Basically, an oscommerce store with a large number of products is sending a multitude of queries to the database each time it is asked to create a product listing page, or perform a search. The number of queries sent to the database is massively bigger than the number of products that you have, as the php script searches through the other associated paramters such as category, manufacturer, prices, tax, attributes etc etc.
Sooner or later, the number of queries will exceed the MAX_JOIN_SIZE setting on your MYSQL database. This is a setting within your hosted MYSQL database that determines how big a demand in mega-bytes the database will tolerate before throwing out an error. Usually, this upper limit is set by your webhost and they dont often like to increase it, due to the corresponding increase in bandwidth and demands upon the server that it causes.
To find out what your setting is, run a query in mysql by clicking on sql query and pasting in the following:
SHOW SESSION VARIABLES LIKE 'max_join_size';
The result will be in kb, so 90000 means your max setting is 9mb worth of databse queries.
If you can change the MAX_JOIN_SIZE, then doing so is a quick fix that wll remove the error message, but it doesnt sort out the underlying issue of too many database queries.
So if your webhost wont let you increase the MAX_JOIN_SIZE, you need to implement the steps included in my fix.
Once the error message has gone, you may find that your store works ok, but is runing very slowly. It may take 10 seconds or more to populate a product list in a given category - obviously unnacceptable on a live store. This reveals the root cause of the original 1104 error message : the site has lots of products and the database is being bombarded with queries that is has to process before it can create the page.
There is an on-going discussion on the oscommerce forums on
how to speed up oscommerce but non-technical users may find this a little heavy.
A quick and easy way to speed up your oscommerce site that I discovered was to temporarily delete the Specials. It transpires that the scripting behind the Specials section is particularly inefficient and should be suspected as the major cause of significant site slow down. There is a discussion here on
optimizing the specials tableI'll be trying this fix myself and reporting back on here how successful it was. I'll also try to translate it into a simple step by step process that should be easier to follow than on the oscommerce discussion.
In the meantime, and if you can live without Specials for a while, go into mysql and run an export query on your Specials table. Save the file on your hard drive for later use, and then go back and select "Empty" to clear out the contents of the table.
Go back to your site and see if it has made a difference, you should find that the site has speeded up quite considerably. If so - Specials was the culprit and you should either follow the link above to optimize it, or wait until I post a laymans guide!
You can run an sql query at a later date to reinstall the specials that you deleted by using the back up file.
Hope this helps someone - and comments welcomed.
want-a-site.co.uk:
low cost website design and hosting - wigan
