The following self join of a table containing 5800 records is crawling:
CREATE TABLE history
( stock VARCHAR(30) NOT NULL, day date NOT NULL, open NUMERIC (6,1) NOT NULL, high NUMERIC (6,1) NOT NULL,
low NUMERIC (6,1) NOT NULL, close NUMERIC (6,1) NOT NULL, volume NUMERIC (12) NOT NULL, PRIMARY KEY
(stock,day)
);
SELECT history.stock, history.day, history.high, history.low, MAX(past_week.high) AS week_high,
MAX(past_month.high)AS month_high
FROM history
INNER JOIN history AS past_month ON (past_month.stock = history.stock
AND past_month.day < history.day AND past_month.day >= (history.day - 30))
INNER JOIN history AS past_week ON (past_week.stock =
past_month.stock AND past_week.day < history.day AND past_week.day >=
(history.day - 7))
GROUP BY history.stock, history.day, history.high, history.low
ORDER BY history.stock, history.day DESC
How can I speed this up?
--
Regards,
Tarlika Elisabeth Schmitz