On 8 Jul 2013, at 8:32pm, peter korinis <kori...@earthlink.net> wrote:
> 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. No, you’re right: one index per table, per SELECT. Pretend you have a phone book with two indexes: surname and first name. You have to look up someone called Miri Kallas. You can use either of the indexes to look up one name but once you’ve done that the other index is useless to you. You have made up some indexes which are useful but not the most useful. Drop those indexes and try to figure out one compound index on each table which would be best for the SELECT you asked about. Remember that this CREATE INDEX it1 ON t1 (c1) CREATE INDEX it2 ON t1 (c2) does not do the same thing as CREATE INDEX it12 ON t1 (c1, c2) Can’t do it for you because I can’t tell which of your columns are from which table. > 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? If you have to tell SQLite which index to use you’re doing it wrong. Make up a good index and SQLite will decide to use it. Especially if you’ve done ANALYZE. > 3. Is there a better way to write this query, for example, would > rearranging the order of AND criteria in WHERE clause improve performance? The query optimizer is meant to do all that for you. However, I suspect that you may understand your query better if you get rid of some of your 'IN' clauses. If you imagine doing 24 (= 2 * 12) different SELECTs, one for each State and HCPSCD, what would your SELECT look like then ? With good indexes it should be possible to make each of those SELECTs execute ridiculously fast. Once you’ve figured out how to do that and make it run fast, /then/ you might want to recombine the query for each State, though perhaps not put both States in the same query. > 4. How do I interpret the 'order' and 'from' in the query plan > results? It’s showing you what each of your indexes is being used for. And what is shows is the neither index is being used for both selecting records and arranging the order of results. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
