SQLite Forum

Understanding memory allocation behavior of SQLite
Login
You will also note that if your schema is thus:

```
CREATE TABLE table_name 
(
   col1 INTEGER, 
   col2 INTEGER, 
   col3 INTEGER, 
   col4 INTEGER, 
   col5 STRING, 
   PRIMARY KEY(col1, col2, col3)
) WITHOUT ROWID
;
CREATE INDEX index_name ON table_name (col4, col5)
;
```

then in addition to all the fiddle-faddle trying to "convert" `col5` from however you present it into a REAL or INTEGER (since you have indicated a preference for storage as an INTEGER, then if that does not work, as REAL, and if that does not work, then "as presented") you are also storing the contents of EVERY ROW TWICE.

Once in the WITHOUT ROWID B-Tree and then yet again in the index_name B-Tree.  The only difference between the two B-Tree's being the `key` and the `payload` (all columns are required in both B-Tree's but the ordering is different), but all records are duplicated completely.

Since it is not possible to insert in-order, then no matter what you do you will be requiring TWO B-Tree rebalance operations FOR EACH ROW (batch) inserted.

Perhaps you need to re-visit your "design".