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
