I've got a view VJU covering 8 tables T1-T8. They all share a column C1 which is indexed in each table:
CREATE VIEW VJU AS SELECT * FROM T1 LEFT JOIN T2 USING (C1) LEFT JOIN T3 USING (C1) LEFT JOIN T4 USING (C1) LEFT JOIN T5 USING (C1) LEFT JOIN T6 USING (C1) LEFT JOIN T7 USING (C1) LEFT JOIN T8 USING (C1); After experiencing a performance issue I ran: EXPLAIN QUERY PLAN SELECT * FROM VJU WHERE C1='ABC'; with the following result: SEARCH TABLE T1 USING INDEX T1_C1_IDX (C1=?) SCAN TABLE T2 SEARCH TABLE T3 USING INDEX T3_C1_IDX (C1=?) SEARCH TABLE T4 USING INDEX T4_C1_IDX (C1=?) SEARCH TABLE T5 USING INDEX T5_C1_IDX (C1=?) SEARCH TABLE T6 USING INDEX T6_C1_IDX (C1=?) SEARCH TABLE T7 USING INDEX T7_C1_IDX (C1=?) SEARCH TABLE T8 USING INDEX T8_C1_IDX (C1=?) I'm unable to understand why T2 isn't using the index. Is it the fact that the table T2 only has 2 rows so SQLite has decided that it's quicker with a scan? Staffan _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
