title: Finding data closest to date?
body: <p>I have a table</p>
  
  <pre><code>CREATE TABLE `symbol_details` (
    `symbol_header_id` int(11) DEFAULT NULL,
    `DATE` datetime DEFAULT NULL,
    `ADJ_NAV` double DEFAULT NULL
  )
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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)) &lt; 10
  AND DATE&lt;=@quarterend
  AND symbol_header_id = 18546 
  ORDER BY  ABS(DATEDIFF(DATE, @quarterend)) ASC LIMIT 1)
  </code></pre>
  
  <p>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? </p>