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
> 
> data_type can be one of:
> (title, description, date_taken, latitude,longitude, duration,
> album,track_nr,genre, artist, year)
> 
> as you've probably guessed this is used to store information about some
> media files.
> 
> Now, I want to select all music details in the following format
> file_id | name | duration | genre | artist | description | album | track_nr
> | year
> 
> it's possible that a music from files does not have any data associated with
> it, in this case i want the name to be in the result set
> 
> 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

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.

If it's still not fast enough, one possibility would be to create a TABLE which 
actually reflects your data.  Presumably one that reflects your layout

file_id | name | duration | genre | artist | description | album | track_nr | 
year

You could use TRIGGERs to make this table change whenever your DATA table 
changes.  Or you could do it in software.  Or you could abandon your DATA table 
entirely.

Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to