Re: [sqlite] To increase search speed

[Ken]

I think method 3 shows the best promise:
   
  Obviously M4 is out since it is in memory only. Besides caching should help 
improve the performance.
   
  The qustion that begs to be asked is: What are your performance goals? And 
what are your performance numbers ie timings for each of the mehtods you 
indicated. 
  Re-run and capture timing for the various selects...
   
  Select distinct AlbumName from MUSIC;
 -> Select distinct albumName from albums;
  
Select distinct ArtistName from MUSIC;
-> select distinc artistName from artists;
  
Select distinct GenreName from MUSIC;
   -> select distinct genreName from genres;
  

 
   
   
  

"Sreedhar.a" <[EMAIL PROTECTED]> wrote:
  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