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

Reply via email to