On 9 Jul 2013, at 6:06pm, peter korinis <kori...@earthlink.net> 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 in claims table
> 3. Find HCPSCD in line table
> So my 2 combined indices would be:
> CREATE INDEX idx_Claim_State_Cnty ON CLAIMS (CLAIM_NO,
> STATE_CD, CNTY_CD)
> CREATE INDEX idx_Line_hcpscd ON LINE (CLAIM_NO, HCPSCD)
Those would be good indexes. You can see how good by using EXPLAIN QUERY PLAN
for your SELECT. Or better still, actually try them out and time the results.
Might be interesting to then reverse the order of the columns in each index and
try those. See if they’re better or worse. Or create lots of indexes, then
use EXPLAIN QUERY PLAN and find out which indexes SQLite decided to use, then
delete the others.
I am deliberately not giving you an absolute answer because you have a good
large set of data for testing and you obviously understand the idea now.
You’ll learn more by trying out several alternatives yourself.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
