I have inherited a fairly clunky database design for the company I work at. This query I'm running takes nearly 3 minutes to run, so I daren't un it on the website!
What I need to do is delete items from the products table where that item does not appear in an order. I was starting off with just selecting the items concerned:
Code:
select p.productID, p.prodName from tProducts p left join tOrderProds op ON p.productID = op.productID where supplierID = 'LO' and op.productID is null;
Could anyone suggest a way to run the query without killing the server? I know left joins are slow, but can't think of another way to do it. BTW I'm stuck in the dark ages running 4.0.
Here's the definition:
Code:
CREATE TABLE tOrderProds (
purchaseOrder int(11) unsigned DEFAULT '0' NOT NULL ,
productID int(11) unsigned DEFAULT '0' NOT NULL ,
prodQty int(11) DEFAULT '0' NOT NULL ,
salePrice float DEFAULT '0' NOT NULL ,
costPrice float DEFAULT '0' NOT NULL ,
KEY purchaseOrder (purchaseOrder)
);
CREATE TABLE tProducts (
productID int(11) NOT NULL auto_increment,
prodCode varchar(10) DEFAULT '' NOT NULL ,
prodName varchar(100) DEFAULT '' NOT NULL ,
supplierID varchar(10) DEFAULT '' NOT NULL ,
supplierCode varchar(20) DEFAULT '' NOT NULL ,
nPrice float DEFAULT '0' NOT NULL ,
PRIMARY KEY (productID),
KEY prodCode (prodCode)
);
dBforums
>
Query optimisation
10-10-05, 12:03
Linear Mode
