On Mon, 8 Jul 2013 15:32:21 -0400
"peter korinis" <kori...@earthlink.net> 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
>
> . 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
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 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.
If HCPSCD is actually an integer, you may save space and time by
declaring it as such.
HTH.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
