On Thu, Jan 28, 2016 at 7:17 PM, Yannick Duch?ne <yannick_duchene at yahoo.fr> wrote:
> I though automatic index was controlled by the pragma `automatic_index`, > which seems to do nothing for me, unless I'm doing it the wrong way, while > `WITHOUT ROWID` on table creations, seems to effectively prevent automatic > indexes, the latter with or without the `PRAGMA automatic_index=0`. > > Is this expected or is this me doing something wrong? > Not sure to understand what you expect this pragma to do, but inserts typically don't involve automatic indexes, which are used only in queries (selects). And automatic indexes are used only if there isn't an suitable index already, and SQLite query planner things one will help. Your insert does have a query, but it fits the PK, so no automatic index is needed. (and you can likely use AUTOINCREMENT to avoid that query in the first place). The 2.4x size difference is kinda expected, since your PK includes your biggest column I suspect (i.e. value), so both the table, and its PK index most store it, doubling at least the memory, and with page waste, clearly can account for the difference. And the time difference can be in-part at least attributed to the increased IO. It seems unusual to use the HTML text in the PK. Are you sure you need it? --DD
