hello, i've faced very long execution of a query.
query is based on 2 temporary tables, because contains subselects, unsupported by mysql. problem: i try to get the good's statuses in my warehouse, by analyzing all orders made for these goods. tables are as following: ===================================================================== CREATE TABLE shop_goods ( id int(11) NOT NULL auto_increment, name varchar(255) default NULL, quantity_on_warehouse int(11) default '0', UNIQUE KEY iid (id) ); CREATE TABLE shop_orders ( id int(11) NOT NULL auto_increment, paid datetime default NULL, cancel datetime default NULL, send datetime default NULL, UNIQUE KEY iid (id) ); CREATE TABLE shop_ordered_goods ( ordernumber int(11) NOT NULL default '0', good int(11) NOT NULL default '0', quantity int(14) NOT NULL default '1', UNIQUE KEY number_good (ordernumber,good) ); ===================================================================== to build final sql, i need to make two temporary tables, for storing quantities of goods what are in "not cancelled not paid" orders: ===================================================================== CREATE TEMPORARY TABLE tmp_not_cancelled_not_paid ( good int(11) not null, quantity int(11) default 0); INSERT INTO tmp_not_cancelled_not_paid SELECT shop_goods.id, sum(shop_ordered_goods.quantity) FROM shop_goods, shop_orders LEFT JOIN shop_ordered_goods on ( shop_ordered_goods.good = shop_goods.id and shop_orders.id = shop_ordered_goods.ordernumber and shop_orders.cancel is null and shop_orders.paid is null ) GROUP BY shop_goods.id; ===================================================================== and in "send" orders: ===================================================================== CREATE TEMPORARY TABLE tmp_send ( good int(11) not null, quantity int(11) default 0); INSERT INTO tmp_send SELECT shop_goods.id, sum(shop_ordered_goods.quantity) FROM shop_goods, shop_orders LEFT JOIN shop_ordered_goods on ( shop_ordered_goods.good = shop_goods.id and shop_orders.id = shop_ordered_goods.ordernumber and shop_orders.send is not null ) GROUP BY shop_goods.id; ===================================================================== and final sql query looks like this: ===================================================================== SELECT shop_goods.id, shop_goods.name as name, shop_goods.quantity_on_warehouse, tmp_not_cancelled_not_paid.quantity, tmp_send.quantity, (tmp_not_cancelled_not_paid.quantity - shop_goods.presence) FROM shop_goods, tmp_not_cancelled_not_paid, tmp_send WHERE /* shop_goods.name like '%smth%'and */ tmp_not_cancelled_not_paid.good = shop_goods.id and tmp_send.good = shop_goods.id /* order by smth */ ===================================================================== issue: running these query on tables with less than 30 entries takes about some seconds, but on real tables with more than 10 000 entries i wait two long that cancel query. question: how can i optimize the query(ies)? i need to match web-server timeout of 30 secs. Artem Mikhailov [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php