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