Problems with product options

[Guest suss]

This is that I get when I click on the product options in admin.

MySQL Error Occured

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

QUERY = SELECT DISTINCT productId, name FROM CubeCart_options_bot RIGHT JOIN CubeCart_inventory ON product = productId ORDER BY name ASC

Can anyone please explain in a easy way that do to?

Thanks

Suss

[Guest suss]

;) Anyone? Please!

[Guest suss]

Bump

Nobody who can help me with this problem, Please.

It's a big problem for me beacuse I can't add any more options to any products. Is where anything I can change to get it work. It has to do with a limit in settings somewhere who make it impossible to add any more.

[Guest kingkoopa]

This is that I get when I click on the product options in admin.

MySQL Error Occured

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

QUERY = SELECT DISTINCT productId, name FROM CubeCart_options_bot RIGHT JOIN CubeCart_inventory ON product = productId ORDER BY name ASC

Can anyone please explain in a easy way that do to?

Thanks

Suss

I have just started to receive the same problem with the admin's Product Options. I think there are too many?? Did you ever get this resolved? Thanks in advance for any help anyone can provide.

[Guest kingkoopa]

I was told this by my host:

Here is the description:

http://dev.mysql.com/doc/refman/5.0/en/usi...-variables.html

Your error message:

> 1104: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use

> SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

There should be filename and line number.

There is a problem with SELECT query. I suggest you to find this in you file and replace " SELECT " with something like (I'm not a SQL guru):

"SET SESSION big_selects=1, SESSION max_join_size=4000000000 ; SELECT "

I'm not sure of this so, please, pass it to CubeCart's guys.

How can I apply this fix? I first get big_selects is not a valid variable, after I remove that bit, I get a syntax error. Any ideas where I can put this two SET SESSION variable statements?

[Guest kingkoopa]

I fixed it!!!

Around line 278 on /admin/products/options.php

Find this:

// get slave products

$slaveProducts = $db->select("SELECT DISTINCT productId, name FROM ".$glob['dbprefix']."CubeCart_options_bot RIGHT JOIN ".$glob['dbprefix']."CubeCart_inventory ON product = productId ORDER BY name ASC");

Add this line before $slaveProducts:

$bigselects = $db->misc("SET sql_big_selects=1");

So it looks like this:

// get slave products

$bigselects = $db->misc("SET sql_big_selects=1");

$slaveProducts = $db->select("SELECT DISTINCT productId, name FROM ".$glob['dbprefix']."CubeCart_options_bot RIGHT JOIN ".$glob['dbprefix']."CubeCart_inventory ON product = productId ORDER BY name ASC");

[Guest Pauly Paul]

Thankyou! I've just encountered the same problem and thanks to this thread have resolved it!

:)

[Guest Jeremy Coates]

The main problem with the query shown in the first post is that it is using an query that will be really slow because the tables are not indexed appropriately.

I had a site with 11000+ products in the inventory table and similar amount in the options_bot table - it used to take over 2 minutes for the query to return, either timing out the browser or causing mysql to slow to a halt.

Resolve by simply adding an index on the CubeCart_options_bot table on the product column:

alter table CubeCart_options_bot add index product (product);

it may also be worth adding an index on the name column in the inventory table too:

alter table CubeCart_inventory add index name (name)

after doing these simple changes we went from 2+ mins to around 15 seconds for the query to run - a good workable time saving - the query could probably be further optimised however it's not something we bothered with as this solution doesn't require any code changes and therefore won't affect upgrading between versions. :sourcerer:

[Guest saldridge60]

Do you know what line this will be at, I am struggling to find where I need to add the below fix? Thanks in advance

The main problem with the query shown in the first post is that it is using an query that will be really slow because the tables are not indexed appropriately.

I had a site with 11000+ products in the inventory table and similar amount in the options_bot table - it used to take over 2 minutes for the query to return, either timing out the browser or causing mysql to slow to a halt.

Resolve by simply adding an index on the CubeCart_options_bot table on the product column:

alter table CubeCart_options_bot add index product (product);

it may also be worth adding an index on the name column in the inventory table too:

alter table CubeCart_inventory add index name (name)

after doing these simple changes we went from 2+ mins to around 15 seconds for the query to run - a good workable time saving - the query could probably be further optimised however it's not something we bothered with as this solution doesn't require any code changes and therefore won't affect upgrading between versions.