Simple Optimization for PHP and MySQL

Here is a list of a few very simple tips for optimizing your php/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.

   

  <?
     ...
     ...
     ...
  ?>
  

  
  is faster than
  

  <? ... ?>
  <? ... ?>
  <? ... ?>
  

 

Don't concatenate when you don't need to.

"SELECT id FROM tabell WHERE id = $_SESSION[id] LIMIT 1"

is faster than:

"SELECT id FROM tabell WHERE id = ".$_SESSION['id']." LIMIT 1"

 

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

'SELECT id FROM tabell WHERE id =
'.$_SESSION['id'].' LIMIT 1'

as the fastest way of concatenating querys.

 

When echoing strings it's faster to separate them by comma instead of dot.

echo "echoing ",$variable," something";

Note: This only works with echo, which is a function that can take several strings as arguments.

 

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++){
   // Some code
}

is faster than:

for($i=0; $i<100/10; $i++){
   // Some code
}

because the value is calculated once instead of ten times.

 

Unset your variables to free memory, especially large arrays.

 

If possible it's of course always better to generate static html pages every time something is updated or as often as an update might be relevant instead of querying the database every time.

Further reading:

• Choosing right data type
• Optimizing Queries with EXPLAIN
• Other Optimization Tips
• A HOWTO on Optimizing PHP

 

Make a comment if you have any tips that I've missed and I will add it.