CREATE INDEX urlIndex ON url(url(50));
CREATE INDEX widIndex ON word_to_url(wid);
CREATE INDEX urlidIndex ON word_to_url(urlid);
CREATE INDEX widUrlidPosIndex ON word_to_url(wid, urlid, pos);
CREATE INDEX urlidWidPosIndex ON word_to_url(urlid, wid, pos);
CREATE INDEX lurlIndex ON link(lurl);

--create temporary url_to_wordcount summary table
drop table url_to_wc;
CREATE TABLE url_to_wc AS
SELECT 
	urlid, count(urlid) as number
FROM 
	word_to_url
GROUP BY urlid;
CREATE INDEX urlidIndex ON url_to_wc(urlid);

--create word_to_url_summary table
drop table word_to_url_summary;

CREATE TABLE word_to_url_summary AS
SELECT 
	w2u.urlid as urlid,
	w2u.wid as wid,
	COUNT(w2u.urlid) as number,
	u2wc.number as total
FROM
	word_to_url w2u,
	url_to_wc u2wc
WHERE
	u2wc.urlid = w2u.urlid
GROUP BY w2u.urlid, w2u.wid;


--create indexes
CREATE INDEX widIndex 
	ON word_to_url_summary(wid);
CREATE INDEX widUrlidIndex 
	ON word_to_url_summary(wid, urlid);
