18.2.7 Choose your data types carefullyThis is NOT the latest copy of this book; click here for the latest version.
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 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!
|
