Thanks for the help. I will go for creating separate indexes for my solution.
Regards, Arjabh On Fri, May 6, 2011 at 3:23 PM, Martin Engelschalk < engelsch...@codeswift.com> wrote: > Hello Arjabh, > > This is a good approach. > > I am not sure what you mean by "affecting a query". Every index > - makes your database larger > - slows down inserts (a little bit) > - speeds up select/update/delete (often dramatically) > > Concerning Columns B an C: > If your deletes are of the form "delete from MyTable where B = 15" and > "delete from MyTable where C = 15", you should create two separate > indexes on B and C. > If your deletes are of the form "delete from MyTable where B = 15 AND C > = 15", you should create one multi-column-index on both columns B and C. > > Martin > > Am 06.05.2011 11:19, schrieb arjabh say: > > Thank you Martin. > > That was very helpful. > > > > According to project requirement, mostly I have to do select query on > Column > > A, and delete query based on columns B and C. > > I created index for the columns B and C as well, and now the queries are > > running pretty fast (completes in ms :D ). > > > > Is this a good approach ? > > Creating indexes affects the insert/update/delete query, isnt it? > > > > Thanks and Regards, > > Arjabh > > On Fri, May 6, 2011 at 2:06 PM, Martin Engelschalk< > > engelsch...@codeswift.com> wrote: > > > >> Hello Arjabh, > >> > >> the autoindex is created for the rowid, because you did not define a > >> column with type "integer primary key". See here: > >> http://www.sqlite.org/lang_createtable.html#rowid > >> > >> SQLite can only use an index for where - clause in your statements if > >> the columns in the where clause are the same as the first columns of an > >> index. > >> As you have an index (the primary key) on columnns A and B, a where > >> clause like "where a = 15" will use this index, A where clause like > >> "where a = 15 and b = 17" will also find the rows using this index. Such > >> a query will be very fast. > >> > >> If you fire a query using other columns or combinations of columns (like > >> "where c = 1"), SQLite has to scan the full table for rows matching > >> this. This will take a long time, depending on the size of the table. > >> > >> You might want to read this for further information: > >> http://www.sqlite.org/queryplanner.html > >> > >> Martin > >> > >> Am 06.05.2011 06:17, schrieb arjabh say: > >> > Hi All, > >>> I have a sqlite database with single table with columns A, B and C. > >>> I have created composite primary key on A and B, and an autoindex was > >>> created (dont know on which columns this index is created). > >>> > >>> When I fire select/delete query with WHERE clause on column A, it is > >>> completed in milliseconds, whereas when I fire the same kind of query > on > >>> basis of columns B or C, it takes couple of minutes. > >>> What is causing this difference? > >>> > >>> A point to note: The table contains millions of rows. > >>> > >>> Thanks and Regards, > >>> Arjabh > >>> _______________________________________________ > >>> sqlite-users mailing list > >>> sqlite-users@sqlite.org > >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
