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 *mega* 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):

```
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 > 100 AND contract_close > 0 AND date > 20090600) a
    INNER JOIN
    (SELECT date, symbol || '1C' AS sym, ret, prc FROM crsp
            WHERE prc > 5 AND date>20090600) c
    ON a.date = c.date AND a.symbol = c.sym
    GROUP BY a.date, a.symbol

```

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).

Is this just a cache size issue? I have a relatively new laptop (MacBook Pro with 4gb RAM). Thanks!

Here's the `.schema`:

```
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);

```