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 learn PHP 7?
Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!
If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!
Next chapter: Declare fields NOT NULL >>
Previous chapter: Choose your data types carefully
Jump to:
Home: Table of Contents
Copyright ©2015 Paul Hudson. Follow me: @twostraws.