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

Reply via email to