On 8 May 2014, at 2:14pm, Clemens Ladisch <clem...@ladisch.de> wrote:
> Woody Wu wrote: >> And, as a general question, for a query in the form of >> select max(a) from table where a < InF and b=B and c=C. >> what's the best index in the case of (1) B is an existed one, or (2) B is >> not exists ? > > That depends. Create two indexes on (b,c,a) and (c,b,a), run ANALYZE, > and check which one gets actually used. To expand on what Clemens wrote, once you get experienced with SQLite you understand better how indexing works and can usually just make up a very good index off the top of your head. But until then this is a typical way of working: 1) Create your tables and populate them with realistic data. 2) Create lots of indexes on the columns used in your SELECT command. 3) Run ANALYZE. 4) Use EXPLAIN QUERY PLAN on your SELECT and see which index SQLite decided to use. 5) Delete the other indexes. The results of the ANALYZE command are saved with your database. It will be used by SQLite for any SELECT or 'WHERE' clause. Unless the character of your data changes, or you make or delete indexes, you only need to run it once, though you can update it at any time by running ANALYZE again. The reason I stated you need realistic, not random, data is that the ANALYZE command figures out not just how many rows each of your tables has but also how 'chunky' the values in them are. There's a difference in the use of an index between a table with 1000 rows, each with a different value, and a table with 1000 rows, each of which is either 'yes' or 'no'. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
