Re: [sqlite] SQLite Use of Indexes

2013-07-14 Thread peter korinis
From: "James K. Lowden" To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite Use of Indexes Message-ID: <20130712161038.b8b4df84.jklow...@schemamania.org> Content-Type: text/plain; charset=US-ASCII On Mon, 8 Jul 2013 15:32:21 -0400 "peter korinis" wrote

Re: [sqlite] SQLite Use of Indexes

2013-07-12 Thread James K. Lowden
On Mon, 8 Jul 2013 15:32:21 -0400 "peter korinis" wrote: > . a CLAIMS table = 43M rows with indices on claim_no and > stateCounty code; and > > . a LINE table = 85M rows with indices on claim_no and HCPCS > (a 5 char text code) > > . Have run ANALYZE > > . Usin

Re: [sqlite] SQLite Use of Indexes

2013-07-09 Thread Simon Slavin
On 9 Jul 2013, at 6:06pm, peter korinis wrote: > So, to implement your suggestion of crafting better indices, here’s my > approach: > 1. First action is joining the 2 tables on claim_no. {Therefore > claim_no should be first row in index for both tables} > 2. Find state and county i

Re: [sqlite] SQLite Use of Indexes

2013-07-09 Thread peter korinis
Thank you Simon for responding to my questions. Your phonebook (FName/LName) analogy clearly explained why 2 indices per table per select won't work. Let me provide a bit more info and a possible attempt to implement your suggestions for better indices. My 'bread and butter' query counts the f

Re: [sqlite] SQLite Use of Indexes

2013-07-08 Thread Simon Slavin
On 8 Jul 2013, at 8:32pm, peter korinis wrote: > 1. Can SQLite use > 1 index per table per SELECT? Seems like using > both indices for each table would be better than the single index per table > as chosen by the plan. No, you’re right: one index per table, per SELECT. Pretend you have a