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
