Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2005
    Posts
    16

    Unanswered: Query optimisation

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

  2. #2
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Quote Originally Posted by 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:

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

    Can you post the EXPLAIN of that SQL query as well as the SHOW INDEX on the tables?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •