Thanks Raghavendra, We use sqlite statements for search.
Best Regards, A.Sreedhar. -----Original Message----- From: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] Sent: Saturday, August 18, 2007 10:13 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] To increase search speed use sqlite statements. regards ragha **************************************************************************** ************** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! **************************************************************************** ************* ----- Original Message ----- From: "Sreedhar.a" <[EMAIL PROTECTED]> Date: Saturday, August 18, 2007 10:52 am Subject: [sqlite] To increase search speed > 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 collatenocase,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,AlbumNamecollate nocase);" > "create index Musicidx2 on MUSIC(AlbumName collate > nocase,ArtistNamecollate nocase,URL collate nocase);" > > This gives better performance than method 1 for the following > searches; > > Selectdistinct AlbumName for particular ArtistName > > SelectTrack for particular AlbumName > > Selectdistinct 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, sampleRateinteger, Channels integer, Filesize > integer,album_id integer,artist_idinteger, 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 > recordwill 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 > > > > > > > > > > > > > > > > > > > > > ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
