Simple Optimization for PHP and MySQL

Here is a list of a few very simple tips for optimizing your PHP and MySQL applications. Keep these in mind while developing.

MySQL

• MySQL is interpreted from right to left so you should put the most significant limiters as far to the right as possible.

• Only select fields you need, instead of selecting * (everything).

• Don’t put things that changes very rarely in the database, instead put it in a global array in some include file.

• Use indexes on the columns in the WHERE clause and on the columns you want to ORDER BY.

• Indexes are great if you search the table alot, but it slows down insertion.

• Use the EXPLAIN command to analyze your indexes.

• If you only want one line as a result from the database you should always use LIMIT 1. This way mysql stops searching when it finds the first line instead of continuing through the whole database, only to find that there weren’t any more lines that matched the query.

• If you use $line = mysql_fetch_array($result) you’ll get two ways of accessing the columns, $line[0] and $line[‘columnname’]. If you only use the $line[‘columnname’] you should use $line = mysql_fetch_assoc($result) instead, then there will not be any $line[int index] array.

• Sometimes mysql_free_result() end up wasting more memory than it saves. Check the difference with memory_get_usage().

• Don’t ask the database for the same stuff over and over again, save the result.

• Use NOT NULL as default value as much as you can, it speeds up execution and saves one bit.

• Use datatypes that fits your data, not too large. For example, INT can hold values up to 4294967295 unsigned, which is often unnecessarily big. Use MEDIUMINT or SMALLINT where applicable.

• Make use of the default values, only insert values that differs from the default values to speed up the insertion.

PHP

• Many code blocks might slow down the interpretation a little bit.

• Don’t concatenate when you don’t need to. “id = $id …” is faster than “id = “.$id.” …”

• Surrounding your string by ‘ instead of ” will make things interpret a little faster since php looks for variables inside “…” but not inside ‘…’. Of course you can only do this when you don’t need to have variables in the string.

• The previous item makes it all boil down to ‘id = ‘.$id.’ …’ as the fastest way of concatenating querys.

• echo is faster than print.
• Set the maxvalue for your for-loops before and not in the loop.
  
  $maxvalue = 100/10;
  for($i=0; $i<$maxvalue; $i++){ … }
  is faster than:
  
  for($i=0; $i<100/10; $i++){ … }
  because the value is calculated once instead of ten times.

• Unset your variables to free memory, especially large arrays.

Source: http://www.dublish.com/articles/10.html

Picture by SpaceX on Unsplash