title: Should an INNER JOIN on a UNION with a GROUP BY take hours in SQLite?
body: <p>I'm trying to learn SQLite and searching for techniques to speed up my query. I see some here trying to go squeeze out ms, when I'm easily in the <em>mega</em> seconds. I have one SQLite db with four tables, although I'm only querying three tables. Here's the query (I am using R to invoke the query):</p>
  
  <pre><code>SELECT a.date, a.symbol, SUM (a.oi*a.contract_close) AS oi, c.ret, c.prc
      FROM (SELECT date, symbol, oi, contract_close FROM ann
              UNION
              SELECT date, symbol AS sym, oi, contract_close FROM qtr
              WHERE oi &gt; 100 AND contract_close &gt; 0 AND date &gt; 20090600) a
      INNER JOIN
      (SELECT date, symbol || '1C' AS sym, ret, prc FROM crsp
              WHERE prc &gt; 5 AND date&gt;20090600) c
      ON a.date = c.date AND a.symbol = c.sym
      GROUP BY a.date, a.symbol
  </code></pre>
  
  <p>I have a an index on each table by date and symbol and just VACUUMed, but it's still very slow, as in an hour plus (and notice that I'm looking for a six month subset... I really want to query back to 2003).</p>
  
  <p>Is this just a cache size issue? I have a relatively new laptop (MacBook Pro with 4gb RAM). Thanks!</p>
  
  <p>Here's the <code>.schema</code>:</p>
  
  <pre><code>CREATE TABLE ann 
  ( "date" INTEGER,
   symbol TEXT,
   contract_type_1 TEXT,
   contract_type_2 TEXT,
   product_type TEXT,
   block_volume INTEGER,
   oi_change INTEGER,
   oi INTEGER,
   efp_volume INTEGER,
   total_volume INTEGER,
   name TEXT,
   contract_change INTEGER,
   contract_open INTEGER,
   contract_high INTEGER,
   contract_low INTEGER,
   contract_close INTEGER,
   contract_settle INTEGER 
  );
  CREATE TABLE crsp 
  ( "date" INTEGER,
   symbol TEXT,
   permno INTEGER,
   prc REAL,
   ret REAL,
   vwretd REAL,
   ewretd REAL,
   sprtrn REAL 
  );
  CREATE TABLE dly 
  ( "date" INTEGER,
   symbol TEXT,
   expiration INTEGER,
   product_type TEXT,
   shares_per_contract INTEGER,
   "open" REAL,
   high REAL,
   low REAL,
   "last" REAL,
   settle REAL,
   change REAL,
   total_volume INTEGER,
   efp_volume INTEGER,
   block_volume INTEGER,
   oi INTEGER 
  );
  CREATE TABLE qtr 
  ( "date" INTEGER,
   symbol TEXT,
   total_volume INTEGER,
   block_volume INTEGER,
   efp_volume INTEGER,
   contract_high INTEGER,
   contract_low INTEGER,
   contract_open INTEGER,
   contract_close INTEGER,
   contract_settle INTEGER,
   oi INTEGER,
   oi_change INTEGER,
   shares_per_contract INTEGER,
   expiration INTEGER,
   product_type TEXT,
   unk TEXT,
   name TEXT 
  );
  CREATE INDEX idx_ann_date_sym ON ann (date, symbol);
  CREATE INDEX idx_crsp_date_sym ON ann (date, symbol);
  CREATE INDEX idx_dly_date_sym ON ann (date, symbol);
  CREATE INDEX idx_qtr_date_sym ON ann (date, symbol);
  </code></pre>