On 21 Aug 2010, at 4:59am, ve3meo wrote: > To pick up on Simon's > suggestion to chunk, I added an integer field 'chunk' to the table: > > UPDATE A SET chunk = (position/10); > > I indexed a bunch of ways to see what the optimiser would do: > > create index idxNameChunkPos ON A(name,chunk,position); > create index idxNamePos ON A(name,position); > create index idxNameChunk ON A(name,chunk); > create index idxName ON A(name); > create index idxPos ON A(position); > create index idxChunk ON A(chunk); > > > Here's how I have interpreted Simon's suggested chunky query: > > select * from A as A1, A as A2 where A1.name=A2.name and > A1.position != A2.position and > A2.chunk between A1.chunk - 1 and A1.chunk + 1 and > A2.position between A1.position - 10 and A1.position + 10 ;
That's exactly what I meant. It allows you to reject everything not in those three chunks immediately, before even working out what 'position - 10' and 'position + 10' are. But looking again at your formulation with 'between' in ... > select * from A as A1, A as A2 where A1.name=A2.name and > A1.position != A2.position and > A2.position between A1.position - 10 and A1.position + 10 ; I think that that might be just as fast, and not require you to work out the chunks, or take up all the space the chunk indexes will take up, or the time it will take to generate them. So we're back to the same old song: Try the simpler solution. If it's fast enough, use it. Only if it isn't fast enough is it worth optimizing. By the way, you might find that swapping the last two lines makes it faster: > select * from A as A1, A as A2 where A1.name=A2.name and > A2.position between A1.position - 10 and A1.position + 10 and > A1.position != A2.position ; But you might not: SQLite's optimizer may already have spotted it. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
