On 23 Jul 2009, at 2:20am, Jim Showalter wrote: > The query looks like this: > > select distinct * from tbl where a <> 0 order by a desc, b desc, c > desc, d desc limit 2; > > where a, b, c, and d are all type INTEGER.
To add to Igor's point, how much use an index is varies depending on how many values there can be for each of its fields. So, for example, if there are lots of possible values for 'b' then there having 'b' in the index can save a great deal of sorting. But if 'b' is likely to have one of just three possible values then sorting on it takes less effort. However, there's another possible payoff: SQLite is clever. If it finds all the values it needs in the index it's using, it doesn't bother to read the record. So if all you really want are the values from fields a, b, c and d, then making one index with all four fields in allows SQLite to do the WHERE, the ORDER BY, and the SELECT purely from the index, without having to read the data record at all. Fortunately, you don't have to change your code to test all this out. Write a couple thousand records without the index and test its speed. Add one kind of index, see how much the file has grown, and see if the speed increase is worth it. Try a different index and try it again. No need to rewrite any of your INSERT or SELECT code. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
