I think you are looking for UNION ALL to avoid creating an ephemeral table to implement the implied DISTINCT
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Peter da Silva Gesendet: Donnerstag, 22. August 2019 17:28 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] [sqlite] Attached databases and union view. Have an existing application that's pushing the limit on how fast it can read data and add it to the database, and thinking of sharding the database file so I can have multiple writers writing to shards of the main tables. ATTACH DATABASE 'shard0.sqlite' as shard0; ATTACH DATABASE 'shard1.sqlite' as shard1; ... CREATE TEMPORARY VIEW sharded_main_table AS SELECT col,col,col...,all_columns_basically FROM shard0.main_table UNION SELECT col,col,col...,all_columns_basically FROM shard1.main_table ...; What's the best way to construct this union view so the query optimizer won't be horribly confused? If I run something like "SELECT count(*) FROM sharded_main_table WHERE ident LIKE 'pattern';" it's about 20 times slower than the same query against the original main_table. Running the query against each shardN.main_table it's actually faster (in total time for all queries in sequence) than running it against the original table. Is there a better way to construct the view, or am I going to get best query performance by making my code shard-aware? All the original indexes on main_table have been copied to the shard databases. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
