1. Home
  2. Forums Index
  3. Server Side / Databases 4:38 am May 21, 2026

Forum Moderators: open

Message Too Old, No Replies

Complex MySQL query across 4 tables

         

pkallberg

7:27 am on Mar 19, 2012 (gmt 0)

10+ Year Member



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!

Thanks.

Dijkgraaf

10:12 pm on Mar 19, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Looking at your FROM clause (formatting to make it more obvious what you are joining together)

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

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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / Databases 4:38 am May 21, 2026