Canman2005 Posted November 26, 2008 Share Posted November 26, 2008 Hi all I have the following QUERY SELECT cart_log.type,cart_log.product_id, cart_log.title, cart_log.prod_quantity_in_packet FROM orders, cart_log, products_supplier_information WHERE ((orders.orders_status = 1) || (orders.orders_status = 2) || (orders.orders_status = 3)) AND cart_log.unique_id = orders.order_unique_id AND products_supplier_information.product_id = cart_log.product_id AND products_supplier_information.supplier_id = 1 GROUP BY cart_log.product_id but it seems to be taking ages to run, sometimes up to half an hour and sometimes it busts the browser and you have to re-start. can anyone suggest anythign which could speed it up? thanks Link to comment https://forums.phpfreaks.com/topic/134340-query-speed/ Share on other sites More sharing options...
Adam Posted November 26, 2008 Share Posted November 26, 2008 How much data is in the table? What's your server like? This won't make much difference to the query speed at all but to help you with future queries: ((orders.orders_status = 1) || (orders.orders_status = 2) || (orders.orders_status = 3)) Can be condensed into: orders.orders_status IN (1, 2, 3) Adam Link to comment https://forums.phpfreaks.com/topic/134340-query-speed/#findComment-699385 Share on other sites More sharing options...
Canman2005 Posted November 26, 2008 Author Share Posted November 26, 2008 A few hundred records in cart_log, around 50 in products_supplier and about 90 in the orders table The server is a dedicated, it's a pretty good spec Is there a better way to re-write the QUERY? Link to comment https://forums.phpfreaks.com/topic/134340-query-speed/#findComment-699386 Share on other sites More sharing options...
Canman2005 Posted November 26, 2008 Author Share Posted November 26, 2008 Is there anyway to assign more memory to scripts that take a long time to run? 70% of the time the page seems to never fully load with that QUERY and normally kills the browser totally Link to comment https://forums.phpfreaks.com/topic/134340-query-speed/#findComment-699401 Share on other sites More sharing options...
unkwntech Posted November 26, 2008 Share Posted November 26, 2008 SELECT cart_log.type,cart_log.product_id, cart_log.title, cart_log.prod_quantity_in_packet FROM orders, cart_log, products_supplier_information WHERE orders.orders_status IN (1, 2, 3) AND cart_log.unique_id = orders.order_unique_id AND products_supplier_information.product_id = cart_log.product_id AND products_supplier_information.supplier_id = 1 GROUP BY cart_log.product_id Is the best I could come up with, I'm thinking there must be a problem with the config on the DB server because this is not a "bad" query so much as it is an exact query, what DBMS are you using? Link to comment https://forums.phpfreaks.com/topic/134340-query-speed/#findComment-699402 Share on other sites More sharing options...
Canman2005 Posted November 26, 2008 Author Share Posted November 26, 2008 I'm using SQL Do you think a server restart would help? Thanks Link to comment https://forums.phpfreaks.com/topic/134340-query-speed/#findComment-699409 Share on other sites More sharing options...
Canman2005 Posted November 26, 2008 Author Share Posted November 26, 2008 I have been looking about online and seen people say that a good way to speed up would be to index your tables, would that help and if so, can anyone give me some help on doing that. Thanks Link to comment https://forums.phpfreaks.com/topic/134340-query-speed/#findComment-699411 Share on other sites More sharing options...
Canman2005 Posted November 26, 2008 Author Share Posted November 26, 2008 getting knowhere fast with this one, has anyone got any ideas? sorry to multi post Link to comment https://forums.phpfreaks.com/topic/134340-query-speed/#findComment-699443 Share on other sites More sharing options...
BloodyMind Posted November 26, 2008 Share Posted November 26, 2008 maybe you can try another SQL clause for joining tables try left join for instance it maybe faster Link to comment https://forums.phpfreaks.com/topic/134340-query-speed/#findComment-699480 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.