Mohit Sindhwani wrote:
>
> Since the database is to be created and deleted in a thread itself, I
> think I may need to do something like:
> * Create in-memory database (":memory:")
> * Attach the main database (from file) as 'mt'
> * Create the temporary table for id_list
> * Insert the user entered IDs
> * Create an index on it
> * Join and get the records of interest (between idlist.id and mt.table.id)
> * Do the processing
>
> Then, when I close the database, the in-memory stuff is all gone. Does
> that sound right?
>
That looks right to me.
There is no need to create an index on the temporary id_list, since you
are going to be doing a full table scan of that table anyway.
select *
from id_list
join mt.table on mt.table.id = id_list.id
order by id_list.id
The only benefit of an index would be if you want the results returned
in id order, then the index would be used to optimize the order by
clause. If that is the case you can get the same effect by declaring the
id column as "integer primary key" in the id_list table. This will
eliminate the index and its redundant duplicate storage of the id_list data.
For fastest operation the mt.table.id should also be an "integer primary
key" column as this will eliminate a rowid lookup operation if it is an
indexed column.
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
