the select statement is
SELECT * from files left join data on files.file_id=data.file_id;
to test the performance i'm only doing
long t1 = System.currentTimeMillis();
Cursor cursor = db.rawQuery(...);
while (cursor.moveToNext()) {
}
android.util.Log.e(TAG, "loaded in: " + (System.currentTimeMillis() -t1));
and the results are
without index: 8143
with index:7039
this is for 1453 entries in resources and 7697 entries in data
the output for explain query plan (without the above index) is
0|0|TABLE files
1|1|TABLE data WITH INDEX sqlite_autoindex_data_1
so it looks like it creates an index automatically and that's why there is
no big improvement
On Wed, Jun 29, 2011 at 2:30 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 28 Jun 2011, at 9:58pm, Lazarus 101 wrote:
>
> >> You should make an index on the columns 'file_id' and 'data_type' from
> the
> >> 'DATA' table. This will allow it to be searched far more quickly. Your
> >> command will be something like
> >>
> >> CREATE UNIQUE INDEX dfd ON data (file_id,data_type)
> >>
> >> Then do the above testing again.
> >
> > tried that and it didn't help much
>
> Something is wrong with your logic or programming. Having this index
> should make a huge difference in the speed of lookup. How are you finding
> the entries in the TABLE called 'data' ?
>
> Actually, as Jan posted, instead of the above index do this one:
>
> CREATE UNIQUE INDEX dfdv ON data (file_id,data_type, value)
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
