4.4. Table Structure and a Crash Course in Planning

As your PHP applications become more complicated, your app's performance will start to play a key role in development. MySQL is a potential performance killer in applications, creating bottlenecks that prevent scripts from executing as quickly as you want them to.

Part of your role as a developer is to know the risks involved with MySQL queries and eliminate as many performance issues as possible. The most common risks I'll show you how to address in this chapter include:

• Poor planning of database tables

• Requests for unnecessary data (using the shortcut selector [*])

4.4.1. Planning Database Tables

Database tables are fairly simple, but interconnected tables can be a hindrance to your scripts' performance if you don't plan them properly. For example, a list of blog entries (entries) and a list of a web site's pages (pages) might look something like this (see Tables 4-3 and 4-4).

You might use another table (entry_pages) to link the entries to the page you want to display them on (see Table 4-5).

Unfortunately, you're storing redundant data in this case, which is both unnecessary and potentially harmful to your application's performance. If you know you're on the blog page, you can use two queries to retrieve the entry data:

<?php
    // Initiate the PDO object
    $dbinfo = 'mysql:dbname=test;host=localhost';
    $user = 'root';
    $pass = '';
    try {
        $db = new PDO($dbinfo, $user, $pass);
    } catch(PDOException $e) {
        echo 'Connection failed: ', $e->getMessage();
    }

    // Creates the first query
    $sql = "SELECT entry
        FROM entry_pages
        WHERE page='Blog'";

// Initialize the $entries variable in case there's no saved data
    $entries = NULL;

    // Retrieves the entries from the table
    foreach($db->query($sql) as $row) {
        $sql2 = "SELECT text
            FROM entries
            WHERE title='$row[entry]'";
        foreach($db->query($sql) as $row2) {
            $entries[] = array($row['title'], $row2['entry']);
        }
    }

    // Display the output
    print_r($entries);
?>

This code returns the following:

Array
(
    [0] => Array
        (
            [0] => Sample Entry
            [1] => This is some text.
        )

    [1] => Array
        (
            [0] => Entry Title
            [1] => This is more text.
        )
)

However, this approach is extremely inefficient because you're retrieving redundant data from the entry_pages and entries tables.

One way to avoid this problem is to add an additional column to tables containing an ID for each row that will automatically increment as rows are added. This allows for less redundancy in data storage. For example, assume you want to revisit your pages and entries by adding an ID column (see Tables 4-6 and 4-7).

Adding an ID column enables you to eliminate entry_pages altogether, which leaves you with an easy way to cross-reference your tables. This means you can rewrite your script to retrieve entries for a particular page far more efficiently:

<?php
    $dbinfo = 'mysql:dbname=test;host=localhost';
    $user = 'root';
    $pass = '';

    try {
        $db = new PDO($dbinfo, $user, $pass);
    } catch(PDOException $e) {
        echo 'Connection failed: ', $e->getMessage();
    }

    $sql = "SELECT title, text
            FROM entries
            WHERE page_id=1";
    foreach($db->query($sql) as $row) {
        $entries[] = array($row['title'], $row['text']);
    }

    print_r($entries);
?>

This code returns an identical result to your previous example, but takes only half as long to execute because it executes only one query instead of the original two.

4.4.2. The Shortcut Selector (*)

MySQL provides a shortcut selector (SELECT *) that enables developers to select all data contained within a table. At first glance, this seems like a convenient way to retrieve data from our tables easily. However, the shortcut selector poses a threat to the performance of your scripts by requesting data that isn't needed, which consumes memory unnecessarily.

To avoid wasting resources, it's considered best practice to request all information required by your scripts explicitly. For example, you shouldn't use this approach if you can help it:

SELECT * FROM entries

Instead, you should write code that goes something like this:

SELECT title, text, author FROM entries

This ensures that information you won't need for a particular script isn't loaded and thus saves memory. This approach has the added benefit of simplifying your code maintenance because you won't have to remember which columns are returned from table if the code needs to be updated in the future—that information is readily available.