Query Speed

[Canman2005]

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

[Adam]

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

[Canman2005]

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?

[Canman2005]

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

[unkwntech]

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?

[Canman2005]

I'm using SQL

 

Do you think a server restart would help?

 

Thanks

[Canman2005]

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

[Canman2005]

getting knowhere fast with this one, has anyone got any ideas?

 

sorry to multi post

[BloodyMind]

maybe you can try another SQL clause for joining tables

 

try left join for instance it maybe faster