My suggestion (using a bigint and flagging the bits instead of using 40 char(1) fields) is only valid if you don't use any of those fields in WHERE clauses in SELECT's. Not that you wouldn't be able to retrieve the entries based on the respective field, but I don't think it would be indeed faster than the way you store the data now. However, I don't know for a fact which method would be faster even when searching, so it's worth a shot.
The process is quite simple. Let's say you have only three char(1) fields: alive, married and usresident. We only need three bits for this one, so we'd use a TINYINT (8 bits) - you on the other hand need 40 bits, so you should use a BIGINT (64 bits). Let's call this field "personflags". We'll assign three fixed bits to the three fields we need to implement. Let's say alive is bit 0, married is bit 1 and usresident is bit 2. Once we decided on this, things are quite simple: we start with 0 in a temporary variable; if we find that alive="y" we add 1 to the temp variable; if married="y" we add 10 (binary) and if usresident="y" we add 100 (binary). Then we store the temporary variable in the database as personflags. Now, to test if, say, the entry denotes an US resident, we must find if bit number 2 is set. That would mean retrieving the bigint and testing if ((personflags & 100)!=0) where 100 is obviously a binary value. I wrote the values in binary so it's easier to see what's happening. I guess you're familiar with binary values, so you know that 10(b)=2(d); 100(b)=4(d). For a generic algorithm you should use the following for bit number $bitno in a field with $maxbits used number of bits (in your case, $maxbits=40): - setting the bit: personflags = personbits | (1 << $bitno) That is, we shift an "1" $bitno bits to the left so we get a value of the form "0000...010...0000". Then we perform a bitwise OR with the original value. In our example, we would shift left with 2 (usresident is bit 2, remember?) and we'd get 100(b). - testing the bit: if (personflags & (1 << $bitno)) This one's simple - unsetting the bit: personflags = personflags & ((1 << ($maxbits+1)) - 1 - (1<< $bitno))) This one's a little trickier. First we shift a "1" to the left as far as it goes and then one. In our three-values example, this would result in 1000(b). Then we subtract 1. This would result in 111(b). From this we subtract the shifted value which is 100(b). We get 011(b). Now we perform a bitwise AND which results in unsetting bit 2 in personflags. Theoretically, instead of subtracting 1 in order to get 111 we could use bitwise negation (!) but I found that's not always reliable - however, it should be a little faster: personflags = personflags & ((!(1 << ($maxbits+1))) - (1<< $bitno))) HTH Bogdan "Greer, Darren (MED)" wrote: > You are correct, they are simple Y/N fields. I am not familiar with the > process you mentioned, but will do some digging. If you have any > information you could give me that doesn't require too much of your > time, I would appreciate it. > > Thanks, > > Darren --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
