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:
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
