Hudzilla.org - the homepage of Paul Hudson

Contents > Performance > Optimising your SQL Wish List | Report Bug | About Me ]

18.2.9 Declare fields NOT NULL

This is NOT the latest copy of this book; click here for the latest version.

Each time you declare a field, MySQL needs one extra bit of data to store whether or not the value is NULL. This is because NULL is not 0, or indeed any value, so the only way for MySQL to know whether a value is NULL or not is to use this special bit.

If you know for a fact that a column will not be NULL, declare it as NOT NULL - this will allow you to save the bit, and stop MySQL from checking that bit every time it checks the value. For example:

CREATE TABLE MyNotNull (ID INT NOT NULL, Name CHAR(20) NOT NULL);

<< 18.2.8 Size vs. Speed		18.2.10 Load data intelligently >>
Table of Contents

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!

Andrey - 07 Sep 2008

Because MySQL should use tri-state logic whenever a field is declared that it may have NULLs - this slows down. A column which is NOT NULL uses two-state logic - faster comparison.

Add comment
Please note that by posting a comment here you are committing it to the public domain. This is important so that others can make use of your code themselves, and also so that I can incorporate helpful notes directly into the main text. Comments are limited to 2000 characters in length.

If you are reporting an error in the content, please tell me directly.