Re: returning the closest date
Posted by:
Sean Nolan
Date: April 07, 2005 03:43PM
It's not slow because of the date calculations, in relative terms they are a tiny part of the execution time for a query like this.
The problem is that you do not have specific join criteria that MySql can optimize. It's a very bad idea to ever have your join based on an IN operation. You want to join tables based on a fairly unique column in one table being equal to a unique column in another table (formally known as a foreign key and a primary key) and you want both of those columns indexed.
I'm not sure I entirely understand your query, but I'd say you want to move the stock table into the list of tables in the main query instead of having it in the subquery, and then specify join criteria that relate the colorid in color to the colorid in stock, and the pid on purchaset to the pid in stock and so on.
Sean Nolan
Subject
Written By
Posted
Re: returning the closest date
April 07, 2005 03:43PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.