The declared type BLOB has the advantage of not messing around with the type of whatever is stored. Specifically, I have strings that look like 16 digit numbers, some with a leading zero, that would get clobbered by NUMERIC affinity; likewise, TEXT affinity would convert integers to text, wasting effort and space. I don't intend to actually store BLOBs in this field.
In my use case, the storage class of the actual values is constant for a given keyid. I could add an index on (value,keyid,location) and SQLite could quickly exclude any TEXT values from a key lookup given an INTEGER value just by looking at the manifest. INTEGER or REAL sort before TEXT. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Simon Slavin Gesendet: Donnerstag, 21. März 2019 17:38 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] Re: [sqlite] filling a key/value table On 21 Mar 2019, at 4:04pm, Hick Gunter <h...@scigames.at> wrote: > CREATE TABLE logidx( keyid INTEGER, value BLOB, timestamp INTEGER, primary > key (keyid, value, location) ) WITHOUT ROWID; Can't answer the question you posed and I'm happy to see Igor could. But I wanted to speak against including a BLOB field in a compound PRIMARY KEY. The PRIMARY KEY gets used a lot when SQLite does anything with a row, and having to match a whole BLOB and then compound it is going to take some time. I think it would be faster to let SQLite do its normal rowid thing and define the above PRIMARY KEY as a UNIQUE INDEX instead. Of course, it may be faster or more convenient to you in some other way in which case I stand corrected. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
