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);
>
> sqlite> select * from sqlite_stat1;
> tags||12
> ev_descr||11
> events||100000
>
> Again, the database contains test data, so I can provide it to you (maybe
> not on the list?). The size is around 7Mb.
>
> PS: I am now a registered sqlite-user.
> PPS: thanks for a great product! Much smaller than Berkeley DB and so much
> powerful!
>
> --
> Martin
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
