Hi everyone, I'm having some trouble performing a (to me) complex MySQL query across 4 tables. So far I've spent about 20 hours working on it to no avail, and would really appreciate any guidance! Here is my code so far:
SELECT news_stories.headline , news_stories.`time` , securities.id , securities.id_bb , securities.name , per_minute_quotes.security_id , max(per_minute_quotes.`timestamp`) AS expr1 , per_minute_quotes.last_price , news_stories.id , news_stories_news_tickers.news_story_id , news_stories_news_tickers.sentiment_score , news_stories_news_tickers.sentiment_confidence FROM per_minute_quotes INNER JOIN securities ON per_minute_quotes.security_id = securities.id, news_stories_news_tickers INNER JOIN news_stories ON news_stories_news_tickers.news_story_id = news_stories.id WHERE news_stories.headline LIKE '%:267 HK' AND securities.id_bb LIKE '267%' AND securities.id = 498 AND per_minute_quotes.`timestamp` <= news_stories.`time` GROUP BY news_stories.headline
So basically I need it to find a security and headlines for that specific security, find the latest price quotes as well as the sentiment score for that headline. When I try to run this, it simply runs for hours without finishing... Could it be that I'm not giving it enough time to finish? When I asked at another forum (without the sentiment score part), I was suggested to use this script, [pastebin.com...] but it gives me an error.
I would be really grateful if someone could help me with this!
Looking at your FROM clause (formatting to make it more obvious what you are joining together)
per_minute_quotes INNER JOIN securities ON per_minute_quotes.security_id = securities.id , news_stories_news_tickers INNER JOIN news_stories ON news_stories_news_tickers.news_story_id = news_stories.id
What you are doing is joining per_minute_quotes to securities and then news_stories_news_tickers to news_stories and then doing an implied Cartesian join on the results of those two. A Cartesian join is joining each row from one result set to each row in another result set. e.g. if you are getting 10 rows from one result set and you are getting 20 rows in another result set and you do a full join you will get 200 rows back. As you can imagine if you have several thousand rows from each result set then you very quickly will get millions of rows.
(Note: It implied Cartesian join because you just separated them with a comma.)
I suspect this is not what you want and you need to have another join clause to join those two sets of tables.
Once you have fixed that you will want to check to see that you have appropriate indexes on the fields you are joining on and those in your where clause.