MySQL Forums
Forum List  »  Newbie

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

Options: ReplyQuote


Subject
Written By
Posted
t c
April 06, 2005 09:23PM
t c
April 07, 2005 02:30PM
t c
April 07, 2005 11:05PM
Re: returning the closest date
April 07, 2005 03:43PM
t c
April 07, 2005 04:28PM
t c
April 07, 2005 04:29PM
t c
April 08, 2005 04:23PM


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.