On 1 Oct 2009, at 8:01am, Atul_Vaidya wrote: > I was told that indexing greatly enhance the speed of SQlite, > however, i > was cautioned that it comes in as an overhead, in terms of increased > size of > the database.The Database, that I am handling, isn't larger.
The database will always be larger each time you add an index. But the index is what allows SQL to find just the records you want rather that having to search through every record. Without indexes you may as well not be using SQL. > Also, I was told, that one should watch, where to create the indexes. > I have created index after creating the table in the > database.Should I > create the index, at the end, when all the data gets itself into the > database ? If you are importing a lot of data to set up your database the first time, it's faster to import all the data first, then create the indexes you need. But if the indexes already exist, it is usually not worth doing 'delete indexes, add more data, make new indexes'. Just remember to make all your changes as one transaction. > Also it would be nice to know,if the inserts are slower when we are > adding > data to the table with indexes already applied to it ? Yes they are. Because SQL has to add the new record to the table and also make another record in each index for the table. But only a very tiny bit slower. It's not normally worth worrying about. > General guidelines about indexing will be greatly appreciated It's all about the balance. If you make very few changes and do a lot of SELECTing you might want one index that's ideal for every SELECT. If you make a great deal of changes to your data but do a SELECT only rarely you want to keep your indexes to a minimum. As you use SQL more you will become better at guessing what is faster, but until then do some testing. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
