Finding data closest to date?

Question

I have a table

CREATE TABLE `symbol_details` (
  `symbol_header_id` int(11) DEFAULT NULL,
  `DATE` datetime DEFAULT NULL,
  `ADJ_NAV` double DEFAULT NULL
)

with ~20,000,000 entries. Now I want to find the ADJ_NAV value closest to the end of the quarter for just one symbol_header_id:

SET @quarterend = '2009-3-31';

SELECT  symbol_header_id AS she, ADJ_NAV AS aend FROM symbol_details
WHERE
 symbol_header_id = 18546 
 AND DATE= (
# date closest after quarter end
SELECT DATE FROM symbol_details
WHERE ABS(DATEDIFF(DATE, @quarterend)) < 10
AND DATE<=@quarterend
AND symbol_header_id = 18546 
ORDER BY  ABS(DATEDIFF(DATE, @quarterend)) ASC LIMIT 1)

When I run the inner "select date" query it returns quickly. Just running the outer query with the correct date filled in instead of the subquery also finishes very quick. But when I run the whole thing it takes forever - something is wrong?

Answer 1

You can probably do without the subquery. Just grab the first row:

SELECT *
FROM symbol_details
WHERE DATE <= @quarterend
AND symbol_header_id = 18546
ORDER BY DATE DESC
LIMIT 1

Answer 2

Seems that the optimizer has some problems to properly evaluate the statement and find the most efficient plan. (In Oracle, I'd ask you to update the statistics, but I'm not sure how the optimizer works in MySQL.)

I'd try some other ways of expressing your statement to see what makes most sense to the optimizer:

• explicitly connect the two symbol_header_ids of the immer and the outer query
• try a SELECT max(date) .. instead of the 'Order By Limit 1'
• try to do a self join of symbol_details

Hope there is a useful idea in here.

Answer 3

Try:

   SELECT t.symbol_header_id,
          COALESCE(t.adj_nav, '0.0') 'adj_nav'
     FROM SYMBOL_DETAILS t
LEFT JOIN (SELECT sh.symbol_header_id,
                  MAX(sh.date) 'max_date'
             FROM SYMBOL_DETAILS sh
            WHERE ABS(DATEDIFF(sh.date, @quarter_end)) < 10
              AND sh.date <= @quarter_end) x ON x.symbol_header_id = t.symbol_header_id
                                            AND x.max_date = t.date
   WHERE t.symbol_header_id = 18546