MySQL Forums
Forum List  »  Optimizer & Parser

Re: Join Query Performance Problme
Posted by: Rick James
Date: February 04, 2012 10:39AM

Each table has exactly one row per stock per day, correct? For example, 100 stocks for a week would 500 rows, correct?

"loadedTime" is really the date of the data, correct? Then, I find the name of the column confusing. It sounds more like it is "the time of day when the information arrived" and it might be days after the day it is referring to.

If I have interpreted your information correctly, the PRIMARY KEY should be a compound index, either of these
PRIMARY KEY (stocksymbol, loadedTime)
PRIMARY KEY (loadedTime, stocksymbol)

Since you are using InnoDB, the order of the data is stored is determined by the PRIMARY KEY. This note is important for huge tables. Are you more likely to "find all info about one stock" or "find all info for one day"? Use the corresponding PRIMARY KEY to achieve efficiency of one or the other of those.

A PRIMARY KEY is a UNIQUE KEY is an INDEX. INDEXes are vital to JOIN performance. Since you want to say "this row in this table corresponds (via stock and date) to that row in that table", say it this way:

... foo f
JOIN bar s ON s.stocksymbol = f.stocksymbol
AND s.loadedTime = loadedTime

So, start with

SELECT ... -- whatever values you need
FROM stockssymbols s
JOIN fastocksdata f
ON s.stocksymbol = f.stocksymbol AND f.loadedTime = s.loadedTime
JOIN pricerelated p
ON s.stocksymbol = p.stocksymbol AND p.loadedTimeTA = s.loadedTimeA
JOIN indicators i
ON s.stocksymbol = i.stocksymbol AND i.loadedTimeTA = s.loadedTimeA
WHERE ... -- additional criteria
GROUP BY ... -- if needed
ORDER BY ... -- if needed
LIMIT --- if needed

If I understand your data correctly, then change your PKs to be
PRIMARY KEY (stocksymbol, loadedTime)
and reformulate your SELECTs to be like what I gave you above.

(There does not appear to be a need for the AUTO_INCREMENT you have in some tables.)

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
February 03, 2012 12:29AM
February 03, 2012 11:27AM
Re: Join Query Performance Problme
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.