Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Lazarus 101
On Tue, Jun 28, 2011 at 8:18 PM, Jan Hudec wrote: > > name TEXT > > > > DATA > > file_id INTEGER REFERENCES files(resource_id) ON DELETE CASCADE, > > data_type TEXT, > > If nothing else, you want to define integer identifiers for the data types > and use integer here. That will save

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Simon Slavin
On 29 Jun 2011, at 2:26am, Lazarus 101 wrote: > the select statement is > SELECT * from files left join data on files.file_id=data.file_id; So you read all the records for the correct file_id, and deal with each one as you find it, ignoring those you don't want. Hmm. I don't see why your

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Lazarus 101
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: " +

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Simon Slavin
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) >>

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Lazarus 101
On Tue, Jun 28, 2011 at 8:18 PM, Jan Hudec wrote: > On Tue, Jun 28, 2011 at 17:33:23 +0300, Lazarus 101 wrote: > > Hi guys, i'm working on an Android app and using sqlite to store some > data > > and i need some help with a query. > > > > I have the following table structure: > > >

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Lazarus 101
On Tue, Jun 28, 2011 at 5:48 PM, Simon Slavin wrote: > > On 28 Jun 2011, at 3:33pm, Lazarus 101 wrote: > > > FILES > > file_id INTEGER NOT NULL, > > name TEXT > > I assume that SQLite has identified 'file_id' as its own 'rowid' column and > made in INDEX for it. > it's

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Jan Hudec
On Tue, Jun 28, 2011 at 17:33:23 +0300, Lazarus 101 wrote: > Hi guys, i'm working on an Android app and using sqlite to store some data > and i need some help with a query. > > I have the following table structure: > > FILES > file_id INTEGER NOT NULL, Do file_ids

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Simon Slavin
On 28 Jun 2011, at 3:33pm, Lazarus 101 wrote: > FILES > file_id INTEGER NOT NULL, > name TEXT I assume that SQLite has identified 'file_id' as its own 'rowid' column and made in INDEX for it. > DATA > file_id INTEGER REFERENCES files(resource_id) ON DELETE CASCADE, > data_type TEXT, > value

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Pavel Ivanov
> This has to run as fast as possible. A left join between these tables is too > slow, for 10.000 entries it takes around 15 seconds just to navigate through > the cursor, if I add a where clause selecting only one kind of data then it > reduces to less than 5 seconds which is acceptable. What

[sqlite] help needed to optimize a query

2011-06-28 Thread Lazarus 101
Hi guys, i'm working on an Android app and using sqlite to store some data and i need some help with a query. I have the following table structure: FILES file_id INTEGER NOT NULL, name TEXT DATA file_id INTEGER REFERENCES files(resource_id) ON DELETE CASCADE, data_type TEXT, value TEXT