On Fri, Apr 8, 2011 at 10:55 AM, Martin Gadbois <mgadb...@gmail.com> wrote:
> Hi there! > > I noticed a heavy slow-down due to automatic indexes. Look at the following > output, where table "events" has 100k entries, and "tags" has ~10 entries: > Thanks for the details in your trouble report. However, you left out the details we need the most, which are (in order): (1) The complete database scheme (2) The content of the sqlite_stat1 and sqlite_stat2 Note that (2) won't exist if you haven't run ANALYZE, which is fine - we just need to know that. > > SQLite version 3.7.4 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> .stats on > sqlite> .timer on > sqlite> SELECT events.rowid FROM tags, events WHERE tags.tag = "security" > AND tags.event_type = events.event_type limit 1; > 1 > Memory Used: 2618712 (max 4011800) bytes > Number of Allocations: 2127 (max 3211) > Number of Pcache Overflow Bytes: 2543752 (max 3910408) bytes > Number of Scratch Overflow Bytes: 0 (max 8384) bytes > Largest Allocation: 48000 bytes > Largest Pcache Allocation: 1272 bytes > Largest Scratch Allocation: 8384 bytes > Lookaside Slots Used: 7 (max 40) > Pager Heap Usage: 2544416 bytes > Schema Heap Usage: 3784 bytes > Statement Heap/Lookaside Usage: 2928 bytes > Fullscan Steps: 5 > Sort Operations: 0 > Autoindex Inserts: *99999* > CPU Time: user 0.220000 sys 0.010000 > sqlite> pragma automatic_index = 0; > sqlite> SELECT events.rowid FROM tags, events WHERE tags.tag = "security" > AND tags.event_type = events.event_type limit 1; > 1 > Memory Used: 2618488 (max 4011800) bytes > Number of Allocations: 2127 (max 3211) > Number of Pcache Overflow Bytes: 2543752 (max 3910408) bytes > Number of Scratch Overflow Bytes: 0 (max 8384) bytes > Largest Allocation: 48000 bytes > Largest Pcache Allocation: 1272 bytes > Largest Scratch Allocation: 8384 bytes > Lookaside Slots Used: 4 (max 40) > Pager Heap Usage: 2544416 bytes > Schema Heap Usage: 3784 bytes > Statement Heap/Lookaside Usage: 2416 bytes > Fullscan Steps: 5 > Sort Operations: 0 > Autoindex Inserts: 0 > CPU Time: user 0.000000 sys 0.000000 > > Re-enabling autoindex yields again poor results with the same query. > I can work around it by adding "ORDER BY events.rowid" to the query. > > Would SQLite "remember" the automatic query instead of creating 99999 > temporary automatic indexes? Is this a bug or mis-use? > I can provide the database if necessary. > > PS: Please CC me directly, my corporate firewall does not allow me to > register to the list (port 8080). > > -- > Martin > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
