18.2.8 Size vs. SpeedThis is NOT the latest copy of this book; click here for the latest version.
Compared to CPUs, hard drives are cheap - you can buy hundreds of gigabytes of space for £200 and still have change, so saving space is not generally an issue. Processors are an entirely different matter, though - adding CPU power is still expensive, so everything we can do to cut down on CPU usage is a good thing, even if that means taking up more hard drive space, right?
Consider this new schema, a modified version of the previous one:
CREATE TABLE mydata (ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Username CHAR(255), Age TINYINT UNSIGNED, JoinDate INT, Homepage CHAR(255), Location CHAR(255), FaveColour CHAR(255), Password CHAR(255), PassRemind CHAR(255))
As you can see, we're now using TINYINT UNSIGNED for our Age field, but the key difference is that we're using CHAR(255) rather than VARCHAR(255). Both hold up to 255 characters of text, but the difference is that VARCHAR only uses as much as space as there are characters for on a row-by-row basis.
Resizing might sound good at first - after all, why leave unused space to waste? The problem lies in the fact that, because VARCHARs can vary in size, MySQL needs to calculate the length of each VARCHAR field in a row so it knows how far to jump to get to the next record. Compare that to using CHAR as the data type, where MySQL can just add 255 (using the above example) to get to the end of one field and the start of another.
|
Want to see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.
My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!
|
