On Sat, Apr 9, 2011 at 2:49 PM, Richard Hipp <d...@sqlite.org> wrote:
> > > On Sat, Apr 9, 2011 at 8:36 AM, Martin Gadbois <mgadb...@gmail.com> wrote: > >> >> If ANALYZE is ran, the speed is fast: there are no auto-index. >> > > There you go. > > Without ANALYZE, the query planner has no idea what your database contains, > and so it guesses that both the tags and events table hold 1,000,000 rows > each. In that case, creating a automatic index definitely improves > performance - it turns an N*N algorithm into NlogN (with N=1000000). But in > your case, with only 12 rows in tags, creating the automatic index is a big > loser. The stats that ANALYZE generate help the query planner to realize > this and thus avoid generating the automatic index. > > You might also try: > > CREATE INDEX some_name ON event(event_type); > > Thank you for your answer. I read the documentation on auto-index, and I do come up with the same conclusion. My original question still holds: is there a way for SQLite to optimize so that the auto-index re-uses the same index it just created so that this type of query runs faster? -- Martin _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
