MySQL Forums
Forum List  »  Optimizer & Parser

Re: Join Query Performance Problme
Posted by: irek kordirko
Date: February 02, 2012 07:02PM

Hello,

could you explain shortly what are expected results of these queries ? What is a bussiness requirement ?
Actually these queries join other three tables only to stocksymbol table, and as a result there is a cross join generated of rows from three remaining tables. The cross join could explode expotentially, depending on number of rows retrieved from indyvidual tables. If you get X rows from the first table, Y from the second and Z from third, then number of rows in the resultset will be X*Y*Z,
for example:
2 * 2 * 2 = 8
5 * 5 * 5 = 125
10 * 10 * 10 = 1000
50 * 50 * 50 = 125000

If the cross join is really requided, then consider whether retrieving all data from these 4 tables is really required (select * ), or maybe selecting only a few columns would be sufficient (select col1,col2, .. colN) - in the latter case database doesn't need to retrieve, store in the buffer and combine whole rows from all 4 tables, but only a small subset of columns, and that should improve performance.

Options: ReplyQuote


Subject
Views
Written By
Posted
1891
February 01, 2012 06:51AM
983
February 01, 2012 11:14AM
1028
February 02, 2012 12:39AM
986
February 02, 2012 06:47AM
Re: Join Query Performance Problme
997
February 02, 2012 07:02PM
982
February 03, 2012 12:29AM
1227
February 03, 2012 11:27AM
993
February 04, 2012 10:39AM
1003
February 06, 2012 06:13AM
1158
February 14, 2012 06:08PM
1005
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.