Declare fields NOT NULL
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);
Next chapter: Load data intelligently >>
Previous chapter: Size vs. Speed
Jump to: Performance Optimising your code Write your code sensibly Use your tools wisely Avoid functions for maximum performance Use the Zend Optimizer Use a PHP code cache Read the manual carefully Get your loops right first Pre-increment where possible Don't think that using references will lower your RAM usage Be wary of garbage collection, part 1 Be wary of garbage collection, part 2 Listen to all errors, big and small Keep up to date Cache array data Compress your output Don't use CGI Don't use dl() Debug your code Cache your pages Use persistent connections Take advantage of new features Compile right Ignore the myths Avoid mod_access if you can Don't fret about Apache 2.0 Optimising your SQL Prioritise your data Optimise your tables Select as little data as possible Use shorter queries where possible Use the EXPLAIN statement Change your hardware Choose your data types carefully Size vs. Speed Declare fields NOT NULL Load data intelligently Consider splitting off variable-length fields Be wary of locks Consider switching OS Spot slow queries Perform joins carefully Index your data Make sure your indexes are being used Normalisation Upgrade MySQL Increase your buffers Keep tabs on your server Lock your tables when appropriate Don't rely on automatic type conversion Benchmark, benchmark, benchmark! Know MySQL's strengths Server PHP Accelerators Caching PHP PHP as a CGI or a module? Size of scripts Summary Exercises Further reading Next chapter
Home: Table of Contents
Follow us on Identi.ca or Twitter
Copyright 2010 Future Publishing Limited (company
registered number 2008885), a company registered
in England and Wales whose registered office is at
Beauford Court, 30 Monmouth Street, Bath, BA1 2BW, UK