Query optimisation

[hessodreamy]

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:

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?

Here's the definition:

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)
);

 

[hessodreamy]

BTW I'm stuck in the dark ages running 4.0.

 

[r937]

you appear to be missing two quite important indexes --

alter table tProducts 
add index suppix(supplierID)

alter table tOrderProds 
add index prodix(productID)

r937.com | rudy.ca

 

[hessodreamy]

Ah yes. Worked a treat. I was wary of adding extra indexes that wouldn't often be used, as I understood that too many indexes were as bad as not enough, but that's a 'discuss...' type issue I'll go read up on. Thanks for the help.