[sqlite] To increase search speed

[Sreedhar.a]

Hi
I am working in 3.3.6.
I have created a table with 40000 records with 12 fields as follows.

"create table MUSIC (Id integer primary key, AlbumName text not null collate
nocase,Track text not null collate nocase,ArtistName text not null collate
nocase,URL text not null collate nocase , Duration integer, TrackFormat text
not null collate nocase, BitRate integer, sampleRate integer, Channels
integer, Filesize integer GenreName text not null collate nocase);"

I will often search for the following fields only.

                                                    Select distinct
AlbumName from MUSIC;
                                                    Select  distinct
ArtistName from MUSIC;
                                                    Select  distinct
GenreName from MUSIC;
                                                    Select  distinct
AlbumName for particular ArtistName
                                                    Select  Track for
particular AlbumName
                                                    Select  distinct
ArtistName for particular GenreName
                              
To obtain nice search speed which method will work fine.
I have tried wilth the following methods.

Method 1:

It's the one described above

Method 2:

By doing indexing.I tried with the following.

    "create  index Musicidx1 on MUSIC(ArtistName  collate nocase,AlbumName
collate nocase);"                   
    "create  index Musicidx2 on MUSIC(AlbumName collate nocase,ArtistName
collate nocase,URL collate nocase);"

This gives better performance than method 1 for the following searches;

                                                                    Select
distinct  AlbumName for particular ArtistName
                                                                    Select
Track for particular AlbumName
                                                                    Select
distinct ArtistName for particular GenreName

Method 3:

Joining Multiple tables.
I have created 4 tables as follows;

            "create table  ALBUMS (id integer primary key,Album
text,unique(Album));"
            "create table  ARTISTS (id integer primary key,Artist
text,unique(Artist));"
            "create table  GENRES (id integer primary key,Genre
text,unique(Genre));"

            "create table MUSIC (Id integer primary key, AlbumName text not
null collate nocase,Track text not null collate nocase,ArtistName text not
null collate nocase,URL text not null collate nocase , Duration integer,
TrackFormat text not null collate nocase, BitRate integer, sampleRate
integer, Channels integer, Filesize integer,album_id integer,artist_id
integer, GenreName text not null collate nocase ,Genre_Id integer);"

Here album_id , artist_id and Genre_Id are the id values of
ALBUMS,ARTISTS,GENRES Tables.
This shows better performance than indexing for the following searches.

 
Select distinct AlbumName from MUSIC;
 
Select  distinct ArtistName from MUSIC;
 
Select  distinct  GenreName from MUSIC;
Method 4:

Inmemory method.I will copy all the content from the temporary database to
inmemory and then performing search.
If I am using this method means then while inserting records , that record
will be inserted into the temporary memory only.
But I want to be inserted in to the original database also. Is there any
other way to do this.

Can any one help to increase my search speed.
Thanks in Advance.
 
Regards,
Sreedhar