9.13 Picking the perfect data typeThis is NOT the latest copy of this book; click here for the latest version.
We have already looked at various possible data types for varying field information, however they are just very rough guidelines - with MySQL, you can ask it what is the optimal data type for a table, and it will analyse its contents and give you an accurate answer. By "optimal", I mean that the recommended field will be the fastest and/or smallest.
The way to do this is appending PROCEDURE ANALYSE() to your SELECT queries. For example, to have MySQL recommend the best fields to use for our usertable table, use this statement:
SELECT * FROM usertable PROCEDURE ANALYSE(1,1);
By default, PROCEDURE ANALYSE() will analyse character fields and, if they contain very few values, recommend an ENUM data type - this is often not wanted, and you can disable that by using PROCEDURE ANALYSE(1,1), which forces MySQL to not use enum fields when they would contain more than one value or take more than 1 byte.
In the output for that query, there'll be a column "Optimal_fieldtype" - that is the important field to look at, and will contain the best field type for that field based upon existing data. That last bit is important - PROCEDURE ANALYSE() can only analyse what is in your table already, which means if it recommends VARCHAR(9), it does so thinking that you are never going to want to include a ten-character string field.
|
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!
|
