On 4 Jul 2013, at 10:29pm, Paul Sanderson <sandersonforens...@gmail.com> wrote:
> create table lookup (index int, start int end int) I assume you missed a comma: create table lookup (index int, start int, end int) But actually it’s a bad idea to use the words 'index' and 'end’ for columns because they're used as reserved words in SQL. So try something like create table lookup (rowindex int, rangestart int, rangeend int) > The takle will be joined on a second table via the index column > > the table is likely to have a few million rows and I will be doing many > thousands of lookups consequtively. My current lookups are of the form > > select * from master as m, lookup as l where x >= start and and x < end and > m.index = l.index Presumably select * from master as m, lookup as l where m.x >= l.rangestart and m.x < l.rangeend and m.rowindex = l.rowindex > i sthere an efficient way of doing this - 30,000 lookups on two table seach > with about 1 million rows is taking about 30 minutes. > > Can I structure my lookup table differently? Don’t do a * unless you actually need * of both tables. List the columns you are actually going to use. > would indexes help in anyway? SELECT * FROM master AS l JOIN lookup AS l ON master.rowindex = lookup.rowindex WHERE m.x >= l.rangestart AND m.x < l.rangeend Good indexes for that would be CREATE INDEX masterRX ON master (rowindex, x) CREATE INDEX lookupRR ON lookup (rowindex, rangestart) You can speed things up even more by doing an ANALYSE once you have data in the tables. This lets SQLite figure out that your lookup table is huge and tune its query plans accordingly. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
