I couldn't care less about the SQL92 standard (or any other SQL standard). What I care about is: * What we do is space efficient on MySQL * What we do is fast on MySQL * What we do works on other databases SQL92 doesn't answer any of those questions for me. Consequently, I have a few goals if we implement status bits for user accounts: * Not having one byte per bit * Not having O(n) searches for common bit-status user queries, if we have such queries * Ability to run on other databases, without significant regard to performance or space efficiency "Make the common case fast." <http://en.wikipedia.org/wiki/Amdahl%27s_law> ----- "Ivan Sergio Borgonovo" <mail@webthatworks.it> wrote:
On Sat, 3 May 2008 19:12:46 +0000 "David Strauss" <david@fourkitchens.com> wrote:
But they probably support another bit-based format.
The smallest thing you can find in SQL92 I think is boolean. As said most DB support bit packing and some have aggregate functions over boolean fields.
char doesn't look as a good choice because on UTF8 may have some overhead, it is ordered according to locale and in need it misses interesting aggregate functions. I don't know SQL standards and enough implementations to know if you can avoid both problems in most DB. Some DB let you define collation and encoding on a field base... some don't even let you define the encoding/collation on a table base.
The smallest SQL92 int is 2 bytes (smallint) someone may think it is too large but it is very well handled by modern CPU and is not affected by locale nor encoding... if you've more statuses...
-- Ivan Sergio Borgonovo http://www.webthatworks.it