Thanks Simon - i'll have a play. tomorrow
On 4 July 2013 22:52, Simon Slavin <slav...@bigfraud.org> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
