In this guide I will explain best practise regarding performance in MySQL, PHP and of course our loved Yii Framework. Since this is a complex topic, I will start with some basics and then expand this guide from time to time. Since this cookbook can become big, you may click on Revision history - there you can see what changes have been made, so you don't miss anything.

Also note that one may find some topics unnecessary because the performance gain is minimal. Still I think it's good to list those topics, even if it's only for a better insight.

>Info: I urge other authors not to edit this cookbook, but of course anyone can make suggestions in the comments section or drop me a private message in forum. Thanks!

Table Of Contents ¶

1. Setting The Best Suited Charset
2. Working With IP Addresses
3. The Type-Safe Operator

• 1. MySQL
  • Setting The Best Suited Charset
  • Working With IP Addresses
• 2. PHP
  • The Type-Safe Operator
• 3. Yii Framework / Applications

1. MySQL ¶

>Info: Some overview description soon...

Setting The Best Suited Charset ¶

When working with UTF-8 charsets (or multibyte charsets in general), it's good practise to set the proper collation charset for each database table.

Example: We have a database with charset utf8_general_ci and a table named Config which contains the column configKey VARCHAR(100) as a primary key. By default, the Config table and all columns will use the same charset as the database - in this case utf8_general_ci. But do we really need this charset for the Config table? We don't need Fulltext search for the configKey column. Also we don't need the column to be case insensitive. In this case, the Config table and all columns with a string type should be set to utf8_bin. Now selects will be faster since MySQL does a binary-comparison of the values/columns.

A real performance boost will show up with a lot of entries in a table. Still there's no argument not to do it for small tables also.

Working With IP Addresses ¶

Most PHP applications I've seen save ip addresses as a string of column type VARCHAR(15). This works well and is already fast when used with an index. Still there is a much better solution.

PHP has the function ip2long() which converts an ip address into a numerical value. The ip address 127.0.0.1 of type string would be converted into 2130706433 of type integer. The function to do the conversion vice-versa is called long2ip(). In MySQL these two functions are called INET_ATON() and INET_NTOA().

The type of the column for such a numerical ip address must be INT(10) UNSIGNED.

So when saving ip addresses as numerical values, you have the benefit of faster selections/sorting and the size of the table will be smaller since INT(10) consumes 4 bytes whereas VARCHAR(15) consumes at least 16 bytes or more (depends on the used charset).

Not that this only works with ipv4 addresses.

2. PHP ¶

>Info: Some overview description soon...

The Type-Safe Operator ¶

PHP has a loose typing system. That means when comparing values, each value can be of a different type.

$foo = '0';

if ($foo == 0) {
   return true;
}
else {
   return false;
}

In this example we defined $foo as a string with value '0'. Even though both values in the if statement are from a different type (string vs. integer), the comparison returns true.

So what basically happens here is that the PHP engine will first determine the type of the first argument $foo and the second argument 0. If these two types don't match, the second argument gets converted into the type of the first argument. After that the comparison will take place.

Since this behavior can be useful in some scenarios, it will always take additional processor time. To save this additional time one may use the type-safe operator (===).

$foo = '0';

if ($foo === 0) {
   return true;
}
else {
   return false;
}

This time the PHP engine expects that both values in the if statement are from the same type. No type-lookup or type-conversion is done now. Since the types differ, false is returned.

If you want to use the type-safe operator all the time, you have to make sure to cast the correct types before doing a comparison.

$foo = '0';

if ((int)$foo === 0) {
   return true;
}
else {
   return false;
}

Here we change the type of $foo from string to integer. Since both types are the same now and the condition matches as well, true is returned.

As you can see it's quite simple. If you use the type-safe operator extensively, you will have a small performance boost and your statements will be less error-prone since you always compare values of the same type. Also note that using the type-safe operator is always faster, even if you cast each value you want to compare to a specific type.

3. Yii Framework / Applications ¶

>Info: Soon