Choosing the right data type for your fields can provide a substantial speed boost, but may or may not be good when it comes to space. For the "select as little data as possible" test above, here's the SQL schema I used:
CREATE TABLE schematest (ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Username VARCHAR(255), Age INT, JoinDate INT, Homepage VARCHAR(255), Location VARCHAR(255), FaveColour VARCHAR(255), Password VARCHAR(255), PassRemind VARCHAR(255));
It is not a complicated schema, as you can see. However, there are a number of ways it can be optimised. Firstly, "INT" is a vastly over-used data type - it means "store any number between -2147483648 and 2147483647". Now, while it might be possible that vitamin pills and a healthy diet will help you live long, but it is not really likely that we will ever use all of that range for our age field. Nevertheless, MySQL does not know that, so we need to be slightly less vague about our Age range.
The "TINYINT" data type stores values from -128 to 127, which is probably enough despite it holding minus values. The "TINYINT UNSIGNED" data type stores values from 0 to 255, which is definitely enough for the foreseeable future, and takes up much less room than a full INT.
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: Size vs. Speed >>
Previous chapter: Change your hardware
Jump to:
Home: Table of Contents
Copyright ©2015 Paul Hudson. Follow me: @twostraws.