homepage Welcome to WebmasterWorld Guest from 54.224.53.192
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Complex MySQL query across 4 tables
pkallberg



 
Msg#: 4430750 posted 7:27 am on Mar 19, 2012 (gmt 0)

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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4430750 posted 10:12 pm on Mar 19, 2012 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved