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
