I read but never got a chance to respond to Scott's question/tip:
Now Feedster makes pretty extensive use of MySQL and as I am reviewing my table structure, something I'm noticing is that I'm using way too large fields all over the place. Now if I read this MySQL man page correctly, I can use a TINYINT to store between -127 and 127. Like a lot of people I suspect, I was just using an INT. That's 4 bytes instead of 1. Not a big savings but if you have this kind of wastage on a number of fields, it adds up.
He goes on to suggest that folks figure out their max values needed for each column and make the right choice. I agree completely. As he suspects, this is something I mention in myMySQL Optimization talk(s) but I tend not to give it a lot of attention. That's often because there are a lot of other (bigger) mistakes that people seem to make.
Anyway, using the most logical column type is smart for two reasons. First, it helps performance. Smaller columns use less space, so more of the data can be cached in memory. It also means fewer disk seeks and that translates to faster disk reads (and writes). Secondly, by using a TINYINT instead of INTEGER, MySQL will limit the size of values you can stick in there. So you're getting a bit more error checking that you don't need to code. I don't think you should rely on it being there, but it's nice to know that it is. Don't write sloppy code.
Posted by jzawodn at April 24, 2003 12:28 PM
Heh.
A) Thanks for replying.
B) I agree that this is a small point and there are bigger ones. But I know I've been wasting space all over the place and w/ > 500,000 rows, its starting to hurt me (I think).
C) Too funny that we had lunch together today and we're communicating by blog.
D) Gliding is cool. Thanks for elaborating on that at lunch.
Best
Scott
