Dennis Cote wrote: > That looks right to me. > Thanks for the quick check, Dennis.
> There is no need to create an index on the temporary id_list, since you > are going to be doing a full table scan of that table anyway. > > select * > from id_list > join mt.table on mt.table.id = id_list.id > order by id_list.id > > The only benefit of an index would be if you want the results returned > in id order, then the index would be used to optimize the order by > clause. If that is the case you can get the same effect by declaring the > id column as "integer primary key" in the id_list table. This will > eliminate the index and its redundant duplicate storage of the id_list data. > This is a very good point that I would not have guessed. My feeling would have been that an index would be needed since I was going to do a join on the data next. But, I see what you mean - something precious learned. Thanks! > For fastest operation the mt.table.id should also be an "integer primary > key" column as this will eliminate a rowid lookup operation if it is an > indexed column. > Yes, this I knew - I always keep an single field integer primary key for almost all my tables! Thanks for the reminder. Best regards Mohit. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
