Please help me understand how query plan chooses an index given sqlite_stat1
table.
I originally had created 4 single-column indices (L3, L4, C3, C4) on this
large 2 table DB.
Following Simon's suggestion to create better/combined/reverse indices, I
created L2 and C2 but query plan still selected the original indices - L3
and C4.
So, I tried creating index C1 (the 'reverse' of C2) and hit the jackpot -
the 3 min query runtime was cut to 1 min using C1 and C3.
{This is counter-intuitive to me - does this mean you do the join after
filtering?}
Trying to repeat the success, I created L1 (the 'reverse' of L2) but no joy;
query plan still selects C1 and L3 indices.
Are there any other possible indices I should try?
Testing with this query:
SELECT COUNT(HCPSCD) CNT, HCPSCD, STATE_CD, CNTY_CD FROM claims , LINE
WHERE CLAIMS.CLAIM_NO == LINE.CLAIM_NO
AND HCPSCD IN
('78451','78452','78453','78454','78469','78472','78473','78481','78483','78
494','78496','78499')
AND STATE_CD = '21'
AND CNTY_CD IN ('060','100','210')
GROUP BY CNTY_CD ;
Query Plan result:
order from detail
0 0 TABLE claims WITH INDEX idx_Cnty_State_ClaimNo
1 1 TABLE LINE WITH INDEX idx_line_claim_no
Here is the sqlite_stat1 table:
Table index
detail
L1 LINE idx_Line_hcpscd_claimNo
86378354 7807 2
L2 LINE idx_Line_ClaimNo_HCPSCD
86378354 2 2
L3 ** LINE idx_line_claim_no
86378354 2
L4 LINE idx_line_hcpscd
86378354 7807
C1 ** CLAIMS idx_Cnty_State_ClaimNo
43428892 112511 12384 1
C2 CLAIMS idx_Claim_State_Cnty
43428892 1 1
1
C3 CLAIMS idx_claims_claim_no
43428892 1
C4 CLAIMS idx_claims_stateCounty
43428892 620413 12384
How do I read the detail above for each index.
What is the logic here?
Thanks much,
peter
From: Simon Slavin <slav...@bigfraud.org>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Subject: Re: [sqlite] SQLite Use of Indexes
Message-ID: <1b21a0a2-dc54-4afe-83e2-0832a4154...@bigfraud.org>
Content-Type: text/plain; charset=windows-1252
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
