ANALYZE not only looks at the tables and the indexes, but also the data in the tables, to work out how 'chunky' the data in each column is. So you can set up your schema perfectly, do ANALYZE with no data in the tables, and SQLite will still not choose the best strategies for search. Also, ANALYZE can take a long time since it has to iterate through all data in the database. So SQLite never does it automatically because it doesn't know when a delay would be convenient to your users.