On 6 Feb 2014, at 8:39pm, Zack Weinberg <za...@panix.com> wrote: > CREATE TABLE url_strings ( > id INTEGER PRIMARY KEY, > url TEXT NOT NULL UNIQUE > ); > > CREATE TABLE canon_statuses ( > id INTEGER PRIMARY KEY, > status TEXT NOT NULL UNIQUE > ); > > CREATE TABLE urls ( > origin INTEGER NOT NULL REFERENCES origins(id), > origin_id INTEGER NOT NULL, > url INTEGER NOT NULL REFERENCES url_strings(id), > UNIQUE (origin, origin_id); > > CREATE TABLE canon_urls ( > url INTEGER PRIMARY KEY REFERENCES url_strings(id), > canon INTEGER REFERENCES url_strings(id), > status INTEGER REFERENCES canon_statuses(id) > );
Why are you indirecting your stored URLs though a special table of [url_strings] ? Why not just put the URLs right there in the [urls] and [canon_urls] tables ? Then you could index those two columns and any search on them would be extremely fast. You would then be able to use a JOIN to find the strings in one table that weren't in the other and it too would be fast. If there's a good reason for the way you're doing it, please excuse me. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users