user3515232 user3515232 - 3 months ago 7
MySQL Question

Nested SELECT is hanging my SQL statement

I'm running a statement that's selecting stock market data from three tables. The last part of the statement is running a SELECT max(date) on a table that contains rows of stock data that is dated. I need the last date for a chosen stock from this table (tbl_asxd_extended.date). The problem is the statement just hangs and I can't work out why.

If I separate the statements, up to the final SELECT and run them independently they run fine! They just don't play well together when combined.

I'm not sure how to troubleshoot this one.

SELECT tbl_asxd_extended.close, tbl_asxd_extended.mcapintra, tbl_asxco.industry, tbl_asxco.company, tbl_watchlist.*
FROM tbl_watchlist
INNER JOIN tbl_asxco ON tbl_asxco.symbol = tbl_watchlist.symbol
INNER JOIN tbl_asxd_extended ON tbl_asxd_extended.symbol = tbl_watchlist.symbol
WHERE user_email='testuser@test.com'
AND tbl_asxd_extended.date =
(SELECT max(tbl_asxd_extended.date) FROM tbl_asxd_extended
WHERE tbl_watchlist.symbol = tbl_asxd_extended.symbol)


Here is an 'EXPLAIN' of the statement

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY tbl_watchlist ALL NULL NULL NULL NULL 9 Using where
1 PRIMARY tbl_asxco eq_ref symbol_2,symbol symbol_2 32 func 1 Using where
1 PRIMARY tbl_asxd_extended ALL NULL NULL NULL NULL 2195 Using where; Using join buffer
2 DEPENDENT SUBQUERY tbl_asxd_extended ALL NULL NULL NULL NULL 2195 Using where

Answer
  SELECT tbl_asxd_extended.close, tbl_asxd_extended.mcapintra, tbl_asxco.industry, tbl_asxco.company, tbl_watchlist.* 
FROM tbl_watchlist 
INNER JOIN tbl_asxco ON tbl_asxco.symbol = tbl_watchlist.symbol 
INNER JOIN tbl_asxd_extended ON tbl_asxd_extended.symbol = tbl_watchlist.symbol 
WHERE user_email='testuser@test.com' 
  AND tbl_asxd_extended.date = 
     (SELECT MAX(tbl_asxd_extended.date) FROM tbl_asxd_extended 
      WHERE tbl_watchlist.symbol = tbl_asxd_extended.symbol) 

this will solve your problem as you're using the max() instead of MAX();

And if possible execute last select query first and store it in any variable say result and just assign result variable to the AND part condition matching