> ................................................
>
> CREATE INDEX samples_by_time ON samples(time);
> CREATE INDEX samples_by_name ON samples(name_id);
> CREATE INDEX samples_by_type ON samples(type_id);
>
> ................................................
Maybe this is dumb idea (sorry for that), but I'll try to write it down:
What if to use an index for an index?
You create N additional talbes as:
CREATE TABLE index_field_N_table (index_field INT PRIMARY KEY, main_table_id
INT);
...
Now you can use those aux tables as indices, ie
SELECT * FROM index_field_N_table LEFT JOIN main_table ON main_table.id =
index_field_N_table.main_table_id WHERE index_field_N_table.index_field = <some
int>;
OR if you need it just for sorting:
SELECT * FROM index_field_N_table LEFT JOIN main_table ON main_table.id =
index_field_N_table.main_table_id WHERE ORDER BY
index_field_N_table.index_field;
SqLite is very smart, so selects will work properly:
sqlite> create temp table xxx (id int PRIMARY key, some_val int);
sqlite> create temp table yyy (id int PRIMARY key, some_val int);
sqlite> explain query plan SELECT * FROM yyy LEFT JOIN xxx ON xxx.id =
yyy.some_val WHERE yyy.id = 1;
selectid order from detail
---------- ---------- ----------
--------------------------------------------------------------------
0 0 0 SEARCH TABLE yyy USING INDEX
sqlite_autoindex_yyy_1 (id=?) (~1 rows)
0 1 1 SEARCH TABLE xxx USING INDEX
sqlite_autoindex_xxx_1 (id=?) (~1 rows)
sqlite> explain query plan SELECT * FROM yyy LEFT JOIN xxx ON xxx.id =
yyy.some_val ORDER BY yyy.id ;
selectid order from detail
---------- ---------- ----------
-----------------------------------------------------------------
0 0 0 SCAN TABLE yyy USING INDEX
sqlite_autoindex_yyy_1 (~1000000 rows)
0 1 1 SEARCH TABLE xxx USING INDEX
sqlite_autoindex_xxx_1 (id=?) (~1 ro
AFAIK you can insert data into several tables simultaneously (we can call this
an 'index creation').
If I'm wrong and SQLite does not support simultaneous inserts into separate
tables, you can use separate database for each table.
To make a creation of 'indices' faster, you can aggregate their data and make
transaction batches much bigger.
Or better yet, if possible memory-wise, dump the data into aux tables at the end
Sorry, I did zero research and I'm not an expert in this field.
I have no clue, how much bigger will your database become and how will it
affect the performance.
I'm not even sure if 'index creation' be any faster... Make your own research
if you like.
Just wanted to share what came to my crazy mind :)
Good luck!
Paul
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
