Optimisation summary

The optimisation chapter ended up being a lot longer than I planned, which in turn means you can't just flick through it and make the appropriate changes to your script! So, I've summarised the various optimisation hints and tips here; for more information on any of these, please go back to the Optimisation chapter.

First up, here are the PHP optimisations:

• Use the Zend OPcache or the Zend Optimizer, depending on your PHP version

• Use the comma operator to join arguments when calling echo

• Priorities optimisation of tight loops for the most payback

• Get into the habit of using pre-increment rather tha post-increment

• Don't rely on references without testing; they are rarely as effective as you'd think

• Get a good understanding of how garbage collection works

• Listen to all errors, big and small - they slow things down

• Store a local pointer to an array element to save indexing into the array each time you need it

• Compress your output to save network bandwidth

• Try to avoid using PHP in CGI mode

• Avoid calling the dl() function

• Use persistent resource where appropriate

• Optimise compilation of your PHP binary if possible

• Avoid mod_access if you can

• Don't fret about Apache 2.0

• As a last resort, try inlining functions

Next, the SQL optimisations:

• Prioritise your queries as LOW_PRIORITY, HIGH_PRIORITY, or DELAYED when it matters

• Don't use SELECT * unless you must

• Use the EXPLAIN keyword to see how MySQL will execute your query - make sure your indexes are being used!

• Load your data before adding indexes is faster than adding indexes first

• Be wary of locks

• Use --log-slow-queries to see where your tables can be optimised

• Increase your buffer sizes so that MySQL can cache more

• Use SHOW STATUS to make sure your MySQL server is in good condition

• Don't create indexes you aren't going to use

• Do as much as your filtering in SQL as you can

• Indexes are good for reads, but bad for writes

• Use OPTIMIZE TABLE and ANALYZE TABLE regularly

• Create your tables with a fixed-table format if possible

• Use the most efficient table type for each table

• Use the best data type, including NOT NULL if appropriate - don't rely on automatic type conversion

• Use default values for INSERT when you can

• Use temporary tables rather than heavy PHP work

• SELECT foo IN (list, of, constants) is very fast

• When joining tables, use numbers instead of strings if you can

Finally, here are the server-related tips:

• Having a dual-CPU machine (or more) makes an enormous difference

• Don't worry about SSE, Hyperthreading, or 64-bit for Apache web servers, but do consider 64-bit for your MySQL server

• Try to get the fastest RAM available: 533MHz RAM is highly recommended

• Most data should be cached in RAM, but don't skimp on hard disks just in case - get S-ATA at the very least

• Resist moving from one machine to two until it becomes financially necessary

• When you network, Gigabit Ethernet is essential, preferably with each machine connected directly to a switch

• Using a round robin DNS system is a simple load-balancing system, but don't forget to put session data in a database

• Blade servers are a cheap and efficient solution for web server systems, but not for SQL servers.

• If you are able, set up one machine as a reverse proxy for your site.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.