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