On Fri, Nov 15, 2013 at 5:14 AM, Pepijn Van Eeckhoudt <pep...@vaneeckhoudt.net> wrote: > I've been looking into the upcoming 'without rowid' feature implementation > to assess if it will have any impact on the OGC GeoPackage specification. > > One of the things I was wondering is what the intended use case of this > feature is. Does it provide a performance boost and/or space savings? If > not, when would you use this?
My take: a) one of the upcoming SQLite4's primary benefits over SQLite3 is that SQLite4 does not have implied rowids, b) because implied rowids were a mistake, c) because implied rowids often resulted in an extra index per-table (when a rowid is not desired), which means: - a bigger db - at least slower data modification statements (to update the extra index) - slower queries if suitable covering indexes are not declared (because of the extra implied join) - slower queries even if suitable covering indexes are declared (because of the extra cache thrashing on writes) d) and because implied rowids almost certainly resulted in extra C code that goes away when the rowids are made explicit (when desired), since, after all, SQLite3 already had code to deal with multiple indexes, and so on. (d) is a big deal, IMO. Removing redundant code and reusing more of the existing code infrastructure means improving maintainability, which leads to faster bug-fix and feature development cycles. (c) is also a big deal: faster updates, faster queries, smaller databases. What's not to like about not having implied rowids? DB navigator-type apps might have been using rowid as a crutch for manual, ex-application DB navigation, but that's all rowids are for such apps: crutches. I can't think of a single case where I would like to have a rowid other than cases where an explicit INTEGER PRIMARY KEY column is natural for solving some task. Even autoincrement: I'd rather do it in the app. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
