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
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 kind
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 TEXT
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 repeat? If
On Tue, Jun 28, 2011 at 5:48 PM, Simon Slavin slav...@bigfraud.org 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 also marked as
On Tue, Jun 28, 2011 at 8:18 PM, Jan Hudec b...@ucw.cz 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:
FILES
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 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: +
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 app
On Tue, Jun 28, 2011 at 8:18 PM, Jan Hudec b...@ucw.cz 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 you some space
10 matches
Mail list logo
