MySQL Forums
Forum List  »  Optimizer & Parser

Re: Join Query Performance Problme
Posted by: johne doe
Date: February 03, 2012 12:29AM

Hi Irek,

Thanks again for your reply.

Why is a cross join being used? That would explain the output of the EXPLAIN command although I thought it was using an inner join when the ON / WHERE conditions were added to it?

Maybe I am confused at how to use the join properly in this instance. I'll explain exactly what I want todo and hopefully you can tell me the correct way todo it.

All I am looking todo is combine the stock data in each of the 4 tables for the given dates. There should only be ~6000 results in total when the join is performed (when it eventually returns it has this many).

To achieve this not using the sql join what i do is as follows.
Load   all   stockssymbols                              ~6000 rows
Load   part  fastocksdata    where loadedTime   = ?     ~6000
Load   part  pricerelated    where loadedTimeTA = ?     ~6000
Load   part  indicators      where loadedTimeTA = ?     ~6000

so now i have four result sets in memory with 6000 rows each (~5MB).
I then simply combine these results together based on the stocksymbol column which is unique in each resultset. That gives me a combined total of ~6000 rows.

This all works in milliseconds.

Do i need to do four separate queries and then join the results of them together in sql somehow or is my join syntax all wrong considering it is using a cross join?

Thanks again,

Options: ReplyQuote

Written By
February 01, 2012 06:51AM
February 01, 2012 11:14AM
February 02, 2012 12:39AM
February 02, 2012 06:47AM
February 02, 2012 07:02PM
Re: Join Query Performance Problme
February 03, 2012 12:29AM
February 03, 2012 11:27AM
February 04, 2012 10:39AM
February 06, 2012 06:13AM
February 14, 2012 06:08PM
February 03, 2012 10:18AM

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.