I'd say generally speaking your way of storing data has no significant downsides. There's just one "but": if each row in your table stores pretty significant amount of data (blobs, long text fields or just lots of different fields) you'd better not make your ItemID INTEGER PRIMARY KEY. Because SQLite stores all rows in the table in the order of rowids. So every time you update your ItemID SQLite would have to move the whole row to a new place. So for the case of big rows I'd suggest to make some other column INTEGER PRIMARY KEY and add unique constraint to your ItemID. It won't hurt your search speed (could make it faster actually) and will make updates faster. Although it will come with a larger size of the database file.
Pavel On Sun, Nov 4, 2012 at 9:26 AM, Dominguez Bonini, David < david.doming...@ikusi.com> wrote: > Hi, > > I have an application where a table is filled with elements whose primary > key is specified at insertion, and is actually a combination of several > independent IDs. Example: ItemID = (id0 << 32) + (id1 << 16) + (id2). > The range covered by each ID guarantees that their sum will never exceed > the 64 bit maximum size of an sqlite primary key. The advantage of this > approach is that a function performing a SELECT statement can pre-compute > the id that needs to be retrieved from the database. This is what I call a > sparse table, meaning that the table will never have more than X items, but > the primary key range is actually much bigger than X. Sorry if my > definitions are not standard, SQL is not my native language :) > > This scheme is used because IDs are usually inserted all at once in a > single batch, and then they have to be regularly updated over a very long > time. So, there are relatively few INSERTS and a LOT of UPDATES and SELECTS. > > I'm wondering if the advantage in search speed obtained by this ID > assignment scheme may somehow be offset by other factors like additional > memory usage, less efficient inserts, etc. Can anyone offer > counterarguments, or recommend a better scheme? > > Regards > > David > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
