I have a large DB and want to improve query performance.

 

Context: the DB contains 2 tables representing nationwide claim data: 

.         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.

Here is the SQL query I use:

SELECT COUNT(HCPSCD) CNT, HCPSCD, STATE_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 IN ('21', '45')

                                                GROUP BY STATE_CD

                                ;

Query Plan results

           Order      From

                0              1              TABLE LINE WITH INDEX
idx_line_hcpscd

                1              0              TABLE claims WITH INDEX
idx_claims_claim_no

 

QUESTIONS:

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.

2.       Is using something like "SELECT . FROM LINE INDEXED BY claim_no
AND INDEXED BY hcpscd" possible? What is the correct syntax to include 2
INDEXED BY this way?

3.       Is there a better way to write this query, for example, would
rearranging the order of AND criteria in WHERE clause improve performance?

4.       How do I interpret the 'order' and 'from' in the query plan
results?

 

Thanks much for your help,

peter 

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to