SQL_BIG_SELECTS=1 ??

[Guest]

Hallo out there,

 

im running 1 shop with an equal devlopment shop.

both of them running the same shop-files.

they r installed on different servers and r using their own database server.

 

one of them (the one with the bigger database server) is running perfect.

The second one get following warning messsage:

 

1104 - The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok

select p.products_id, p.products_image from orders_products opa, orders_products opb, orders o, products p where opa.products_id = '141' and opa.orders_id = opb.orders_id and opb.products_id != '141' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit 6

[TEP STOP]

 

---------------------

 

Both shops had been online for 2 weeks without any problems,

now the second one seems to have a problem with the growing database, but why?

and how to solve this problem ?

 

thx

[warrenthewindmill]

select p.products_id,

p.products_image

from orders_products opa,

orders_products opb,

orders o,

products p

where opa.products_id = '141'

and opa.orders_id = opb.orders_id

and opb.products_id != '141'

and opb.products_id = p.products_id

and opb.orders_id = o.orders_id

and p.products_status = '1'

group by p.products_id

order by o.date_purchased desc limit 6

 

I'm not sure what you are trying to do here but a self join on the order products table with a != criteria may have something to do with it?

Why are you joining this table to itself? Can you explain in English what you are trying to achieve with this SQL and maybe we can find a a more efficient way of writing it.

[Guest]

huh, sorry i cant explain what "oscommerce" try to achieve with this SQL.

its not my code. its from the oscommerce MS1 itself.

 

the source for this error is the "also_purchased_products.php" in the modul dir. iam working with the MS1 since 2-3 weeks, never seen this error before.

 

i think it depends on the version of MySQL. 3.23.54 is ok, 3.23.51 produce this error.

 

Sorry, cant help you to develop a more efficient way. Im not so familiar with SQL/PHP.

[Larskovitch]

I get exactly the same error-message ! I know it has something to do with time-out server-settings, but do not know how to resolve this... Anyone??

[Larskovitch]

Oke, according to the error-message i copied the next phrase into line 14 of also_purchased_products.php (catalog/includes/modules):

 

SET OPTION SQL_BIG_SELECTS=1

 

So now i end up with a altered line 14 looking like this:

 

$orders_query = tep_db_query("select p.products_id, p.products_image from " . TABLE_ORDERS_PRODUCTS . " opa, " . TABLE_ORDERS_PRODUCTS . " opb, " . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p where opa.products_id = '" . SET OPTION SQL_BIG_SELECTS=1 . $HTTP_GET_VARS['products_id'] . "' and opa.orders_id = opb.orders_id and opb.products_id != '" . $HTTP_GET_VARS['products_id'] . "' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit " . MAX_DISPLAY_ALSO_PURCHASED);

 

If i now refresh my products_info page it looks a lot better, BUT..... i get a parse error now and don't know where it is going wrong. Maybe i put the "SET OPTION SQL_BIG_SELECTS=1" in the wrong place?

 

Anyone, please?

[WarrenSmith]

Yes I get this same error but it seems tied to a growing database. Some servers handle the load better than others before they give this "1104 - The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok". This is just casual observation but now I am getting peeved at not understanding this issue. Much less knowing exactly what I can do about it. Surely others out there know something authoritive about this.

[BadBuLL]

Any1 got solution for this error yet? Or is the only solution to reset SQL server?

[WarrenSmith]

yes i wish there were a solution for this as my site host (netfirms.com) has continued problems relating to this issue. Anybody?

[orangello]

yes i wish there were a solution for this as my site host (netfirms.com) has continued problems relating to this issue. Anybody?

 

Did anyone ever get a solution for this? My host is also netfirms. Just did the Attributes Sets Plus contribution and am getting this error.

[mpotter8585]

I had this same problem after our hosting company upgraded their version of MySQL. the issue has to do with the result set being loo large.

 

I resolved the issue by modifying catalog\product_info.php, and adding the line

 

tep_db_query("SET SQL_BIG_SELECTS=1");

 

before line 74: $product_info_qUery = tep_db_query("select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_quantity, p.products_image, pd.products_url, p.products_price, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");

 

This is a mysql setting you are setting at runtime, and if your host allows you to modify this setting at the server level, that would be a better solution, as you dont have to modify the code.

 

 

Hallo out there,

im running 1 shop with an equal devlopment shop.

both of them running the same shop-files.

they r installed on different servers and r using their own database server.

one of them (the one with the bigger database server) is running perfect.

The second one get following warning messsage:

1104 - The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok

select p.products_id, p.products_image from orders_products opa, orders_products opb, orders o, products p where opa.products_id = '141' and opa.orders_id = opb.orders_id and opb.products_id != '141' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit 6

[TEP STOP]

---------------------

Both shops had been online for 2 weeks without any problems,

now the second one seems to have a problem with the growing database, but why?

and how to solve this problem ?

thx

[crash3903]

I had this same problem after our hosting company upgraded their version of MySQL. the issue has to do with the result set being loo large.

 

I resolved the issue by modifying catalog\product_info.php, and adding the line

 

tep_db_query("SET SQL_BIG_SELECTS=1");

 

before line 74: $product_info_qUery = tep_db_query("select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_quantity, p.products_image, pd.products_url, p.products_price, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");

 

This is a mysql setting you are setting at runtime, and if your host allows you to modify this setting at the server level, that would be a better solution, as you dont have to modify the code.

 

Or have it like this towards the start of that file

 

<?php

/*

$Id: product_info.php,v 1.98 2003/09/02 18:52:33 project3000 Exp $

 

osCommerce, Open Source E-Commerce Solutions

http://www.oscommerce.com

 

Copyright © 2003 osCommerce

 

Released under the GNU General Public License

*/

 

require('includes/application_top.php');

 

require(DIR_WS_LANGUAGES . $language . '/' . FILENAME_PRODUCT_INFO);

// SQL_BIG_SELECTS = 1 - added to solve 1104 MYSQL error message

tep_db_query("set sql_big_selects=1");