Query optimisation - MySqlTalk.com

10-10-2005, 11:58 AM

hessodreamy

Junior Member

Join Date: May 2005

Posts: 9

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

Last edited by hessodreamy : 10-10-2005 at 12:01 PM.

Bookmark this post with Del.icio.us

10-11-2005, 01:36 AM

Azkaban

Senior Member

Join Date: Jul 2004

Posts: 167 Azkaban is on a distinguished road

Re: Query optimisation

Quote:

Originally Posted by hessodreamy

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;

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

How many records in each database?

Have you tried formulating the data in different ways and running EXPLAIN on them? The best way to improve your queries is running EXPLAIN, learning what columns are used to select the data. Using the most efficient way possible once you see how many records had to be sorted depending on your wording of the query. And adding indexes where appropriate then rerunning the EXPLAIN.

The above is just some general rules. In your case, sounds like an index on supplierID will do wonders. Try it and report back.

__________________
** Most misspellings intentional to combat spaham filterz**

Bookmark this post with Del.icio.us

10-11-2005, 04:18 AM	#3
hessodreamy Junior Member Join Date: May 2005 Posts: 9	Re: Query optimisation Ah yes. Indexes on tProducts.supplierId and tOrderProds.productID 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.
	Bookmark this post with Del.icio.us

10-11-2005, 12:08 PM

Azkaban

Senior Member

Join Date: Jul 2004

Posts: 167 Azkaban is on a distinguished road

Re: Query optimisation

Quote:

Originally Posted by hessodreamy

I understood that too many indexes were as bad as not enough

Personally don't agree with that comment. They both operate at different times.

Not enough indexes slows you down when you are doing a SELECT and need an index.

Too many indexes slows you down a bit when you are inserting a new row in the database.

If you have an application that has to be fast, inserts many new rows simulateneously and only rarely does a SELECT, than many indexes can be problematic.

If on the other hand you rarely add a new row but SELECT all the time, indexes will be barely be felt.

Also a rule of thumb for indexes. You want to use them on columns in the WHERE field of your common selects. Use EXPLAIN on your query to find out if your index is being used where you think it is.

__________________
** Most misspellings intentional to combat spaham filterz**

Bookmark this post with Del.icio.us

FAQ	Members List	Calendar	Search	Today's Posts	Mark Forums Read

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Will Paypal you $20 to write this one line select query	Synpax	General	4	02-16-2005 07:58 PM
MS Access pass through query - ODBC error	Snowsride	SQL syntax	0	12-22-2004 10:04 AM
Help - SELECT COUNT of a UNION Query	Vmusic	SQL syntax	0	12-16-2004 10:21 AM
Creating a conditional query	gcornish	SQL syntax	8	08-16-2004 12:24 AM
Using one query to search from two tables	Justice	SQL syntax	1	06-25-2004 12:34 PM

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)


	Web http://www.mysqltalk.com