StoreCore

Performance guidelines

Performance is one of the StoreCore design principles. This StoreCore developer guide contains several do’s and don’ts on PHP and MySQL performance.

This documentation is a work in progress. It describes prerelease software, and is subject to change. All code is released as free and open-source software (FOSS) under the GNU General Public License.

Do your own math

Letting the server recalculate a fixed value over and over again, is lazy. Simply calculate the fixed value once yourself. Add a comment if you would like to clarify a given value.

Incorrect:
setcookie('language', $lang, time() + 60 * 60 * 24 * 30, '/');
Correct:
setcookie('language', $lang, time() + 2592000, '/');
Correct:
// Cookie expires in 60 seconds * 60 minutes * 24 hours * 30 days = 2592000 seconds
setcookie('language', $lang, time() + 2592000, '/');

Order database table columns for performance

In some databases, it is more efficient to order the columns in a specific manner because of the way the disk access is performed. The optimal order of columns in a MySQL InnoDB table is:

  • primary key
  • combined primary keys as defined in the KEY order
  • foreign keys used in JOIN queries
  • columns with an INDEX used in WHERE conditions or ORDER BY statements
  • others columns used in WHERE conditions
  • others columns used in ORDER BY statements
  • VARCHAR columns with a variable length
  • large TEXT and BLOB columns.

When there are many VARCHAR columns (with variable length) in a MySQL table, the column order MAY affect the performance of queries. The less close a column is to the beginning of the row, the more preceding columns the InnoDB engine should examine to find out the offset of a given one. Columns that are closer to the beginning of the table are therefore selected faster.

Store DateTimes as UTC timestamps

Times and dates with times SHOULD be stored in Coordinated Universal Time (UTC). The following examples illustrate this requirement with column definitions in a CREATE TABLE statement.

Incorrect:
`date_added`  DATETIME  NOT NULL
Correct:
`date_added`  TIMESTAMP  NOT NULL  DEFAULT CURRENT_TIMESTAMP
Incorrect:
`date_modified`  DATETIME  NOT NULL
Correct:
`date_modified`  TIMESTAMP  NOT NULL  ON UPDATE CURRENT_TIMESTAMP

When there are two timestamps in the same database table, the logical thing to do is setting date_added to DEFAULT CURRENT_TIMESTAMP for the initial INSERT query and date_modified to ON UPDATE CURRENT_TIMESTAMP for subsequent UPDATE queries:

`date_added`     TIMESTAMP  NOT NULL  DEFAULT CURRENT_TIMESTAMP,
`date_modified`  TIMESTAMP  NOT NULL  DEFAULT '0000-00-00 00:00:00'  ON UPDATE CURRENT_TIMESTAMP

This, however, only works in MySQL 5.6+. Older versions of MySQL will report an error: “Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause”.

The workaround currently implemented in StoreCore is to set the DEFAULT value for the initial INSERT timestamp to '0000-00-00 00:00:00' and only use the CURRENT_TIMESTAMP for a subsequent ON UPDATE:

`date_added`     TIMESTAMP  NOT NULL  DEFAULT '0000-00-00 00:00:00',
`date_modified`  TIMESTAMP  NOT NULL  DEFAULT CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP

Don’t cast MySQL integers to strings

String equality comparisons are much more expensive database operations than integer compares. If a database value is an integer, it MUST NOT be treated as a numeric string. This holds especially true for primary keys and foreign keys.

Incorrect:
$sql = "
    UPDATE sc_addresses
    SET customer_id = '" . (int)$customer_id . "'
    WHERE address_id = '" . (int)$address_id . "'";
Correct:
$sql = '
    UPDATE sc_addresses
    SET customer_id = ' . (int)$customer_id . '
    WHERE address_id = ' . (int)$address_id;

The first PHP statement creates an SQL expression with numeric strings and the second statement an expression with true integer values:

Incorrect:
UPDATE sc_addresses
   SET customer_id = '54321'
 WHERE address_id  = '67890';
Correct:
UPDATE sc_addresses
   SET customer_id = 54321
 WHERE address_id  = 67890;

Don’t close and immediately re-open PHP tags

A common mistake in PHP templates and MVC views is closing and immediately re-opening PHP-tags.

Incorrect:
<?php echo $header; ?><?php echo $menu; ?>
Incorrect:
<?php echo $header; ?>
<?php echo $menu; ?>
Correct:
<?php
echo $header;
echo $menu;
?>
Correct:
<?php
echo $header, $menu;
?>
Correct:
<?php echo $header, $menu; ?>

Return early

Once the outcome of a PHP method or procedure has been established, it SHOULD be returned. The examples below demonstrate this may save memory and computations.

Incorrect:
public function hasDownload()
{
    $download = false;

    foreach ($this->getProducts() as $product) {
        if ($product['download']) {
            $download = true;
            break;
        }
    }

    return $download;
}
Correct:
public function hasDownload()
{
    foreach ($this->getProducts() as $product) {
        if ($product['download']) {
            return true;
        }
    }
    return false;
}

Adding a temporary variable and two lines of code for a simple true or false does not really make sense. First breaking from an if nested in a foreach loop doesn’t make much sense either if you can just as well return the result immediately.