On 16 Jul 2009, at 6:22pm, MADHAVAN VINOD wrote: > Suppose, if I don't have index, is this the desired behavior of Sqlite > to take this much time to fetch just 10 records or am I missing > something here.
Your first post states quite clearly '5) No INDEX created.'. Without any index on your table, how else could the computer do the SELECT command ? I think you don't know what indexes are for, and you haven't thought through the task you are giving the computer. It may be that at this stage you should stop programming and read some books on how database systems work, to understand how best to use indexes. If there are no useful indexes on your table, the computer has to read every row of the table to find out which rows satisfy your SELECT command. It then has to work out which rows satisfy your 'WHERE' clause, and then sort the rows it doesn't reject into the right order. Naturally this takes a lot of time. The whole idea of indexes is to allow your computer to quickly find which rows it needs without having to read every single row. So ideally you define an index which lets it select the right records in the right order without having to read any data from the table. Your problem is this: you write in your original post 'So my WHERE clause is like "CurrTime <= ExpireTime AND CurrTime >= NextProcessingTime AND a=1 AND b < c"' I am going to assume that all of 'CurrTime', 'ExpireTime', 'NextProcessingTime', 'a', 'b', 'c' are fields in your table. Your next problem is that you use, for example, 'b < c' in your SELECT command so you are asking for a test to done for every row in the table to decide if the row should be considered for the SELECT command: each time you do a SELECT command it has to read every row in the table and check to see whether 'CurrTime <= ExpireTime', 'CurrTime >= NextProcessingTime', and 'b < c'. Since you say you have 100,000 that's 100,000 read instructions and 300,000 pieces of mathematics it has to do every time you do your SELECT command. The idea of indexes is to have as much of possible of that done when the record was saved in the first place, so it doesn't have to be done every time you use a SELECT command. If 'CurrTime' is a column of your table you could calculate all of your WHERE clause when you write each record and write the result into another column of the table, then index that column. If it isn't, then you should certainly provide an index on ExpireTime or NextProcessingTime or 'a', whichever one allows the SELECT to reject the most records most quickly. And with a good understanding of the data in your table you could make up an compound index that makes the SELECT work even faster. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
