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
