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
