> > 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).
>
> I though it was responsible for the `sqlite_autoindex_"table_name"_N`
> which gets generated and augments the DB size. That's the name and the
> documentation which makes me believe this. I can see these index in
> sqlitebrowser and at the file size.
When you create a table thusly:
create table x (x primary key, y, z);
you are creating a rowid table with columns x, y, an z. You are also saying
that you want x to be the primary key. Therefore, you will get a table called
x containing (ROWID, X, Y, Z). This is a btree where the index is the rowid.
In order to implement you PRIMARY KEY, an index must be constructed containing
the column X and ROWID in the table where the value of X is found. This index
will be called sqlite_autoindex_x_1 because it is the first index created
automatically on table x.
You could achieve the same result as follows:
create table x (x, y, z);
create unique index pk_x_x on x(x);
except that now you have given the index on x an explicit name and one does not
have to be manufactured for you.
Similarly, if you used:
create table x (x primary key, y unique, z);
you would get a table containing (rowid, x, y, z) and an sqlite_autoindex_x_1
implementing the unique key on x, and an sqlite_autoindex_x_2 implementing the
unique constraint (index) on y. If you wanted to explicitly specify the names
of the indexes then you could say:
create table x (x, y, z);
create unique index pk_x_x on x(x);
create unique index uk_x_y on x(y);
The end result is the same. These are indexes that you have DECLARED that you
want and are entirely different from automatic indexes used to increase the
performance of queries.
when you create a table thusly
create table x (x primary key, y, z) without_rowid;
you are creating a table without a rowid. The table x consists entirely of an
index where the indexed value is x, and the index "result" is y, z. There is
no actual table.
The problem with your original query is the use of INSERT or REPLACE. Do not
do that. REPLACE is implemented as a DELETE (which means that all the indexes
need to be updated and the tree's rebalanced) followed by an INSERT (which
requires all the indexes to be updated and the trees to be rebalanced).
Instead you should determine what you want to do and do it. For example:
cr.execute('BEGIN IMMEDIATE')
try:
rowid = cr.execute('select rowid from element_attribute_values where
element=? and attribute=? and value=?', (element, attribute,
value)).fetchone()[0]
cr.execute('update element_attribute_values set count=count+1 where rowid=?',
(rowid,))
except:
cr.execute('insert into element_attribute_values values (?, ?, ?, 1)',
(element, attribute, value))
cr.execute('COMMIT')
You can speed it up even more by moving your transactions out to cover multiple
operations.
In this way you never need to delete then re-insert the same row.
Remember, the only way to optimize I/O is not to do it.
