From: "James K. Lowden" <jklow...@schemamania.org>
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" <kori...@earthlink.net> wrote:
///////////// thanks James, peter ////////////////////
> . 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
> . Using Win7
> Want to count occurrences of given HCPCS codes for given states.
SELECT COUNT(HCPSCD) CNT, HCPSCD, STATE_CD FROM claims ,
LINE
WHERE CLAIMS.CLAIM_NO == LINE.CLAIM_NO
-- ^^ == is not SQL
//////// right, a little python slipped in /////////////
AND HCPSCD IN ( '78451'
, '78452'
, '78453'
, '78454'
, '78469'
, '78472'
, '78473'
, '78481'
, '78483'
, '78494'
, '78496'
, '78499' )
AND state_cd IN ('21', '45')
GROUP BY STATE_CD
>From a design perpective, you might want to think about what the IN
clause is all about. Whatever they have in common probably belongs in
a table, so you'd be able to say
SELECT COUNT(HCPSCD) CNT, HCPSCD, STATE_CD
FROM claims, LINE, HPs
WHERE CLAIMS.CLAIM_NO = LINE.CLAIM_NO
AND HCPSCD = HPs.code
AND HPs.category = 'foo'
AND state_cd IN ('21', '45')
GROUP BY STATE_CD
That suggests HCPSCD as the column to index; I suppose you have a lot
more different HCPSCDs //////////// there are >7000 HCPSCDs ; I'm interested
in ~100 in 12 groups /////////////
than STATE_CDs. If the HPs table is indexed by
(category, code), then the join is on two ordered sets instead of an
extended OR.
/////// would joining 3 tables give better response ?? //////////////
If HCPSCD is actually an integer, you may save space and time by
declaring it as such. /////////// HCPSCD is NOT an integer //////////////
HTH.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
