Recipe 11.7. Caching Calculated Results in Summary Tables

11.7.1. Problem

You need to collect statistics from log tables that are too large to efficiently query in real time.

11.7.2. Solution

Create a table that stores summary data from the complete log table, and query the summary table to generate reports in nearly real time.

11.7.3. Discussion

Let's say that you are logging search queries that web site visitors use on search engines like Google and Yahoo! to find your web site, and tracking those queries in MySQL. Your search term tracking log table has this structure:

CREATE TABLE searches (   searchterm    VARCHAR(255) NOT NULL,  # search term determined from HTTP_REFERER                                         parsing   dt            DATETIME NOT NULL,      # request date   source        VARCHAR(15) NOT NULL    # site where search was performed );

If you are fortunate enough to be logging thousands or tens of thousands of visits from the major search engines per hour, the searches table could grow to an unmanageable size over a period of several months.

You may wish to generate reports that illustrate trends of search terms that have driven traffic to your web site over time from each major search engine so that you can determine which search engine to purchase advertising with.

Create a summary table that reflects what your report needs to display, and then query the full dataset hourly and store the result in the summary table for speedy retrieval during report generation. Your summary table would have this structure:

CREATE TABLE searchsummary (   searchterm    VARCHAR(255) NOT NULL,  # search term   source        VARCHAR(15) NOT NULL,   # site where search was performed   sdate         DATE NOT NULL,          # date search performed   searches      INT UNSIGNED NOT NULL,  # number of searches   PRIMARY KEY (searchterm, source, sdate) );

Your report generation script can then use PDO to query the searchsummary table, and if results are not available, collect them from the searches table and cache the result in searchsummary:

 $st = $db->prepare('SELECT COUNT(*)                      FROM                         searchsummary                      WHERE                         sdate = ?'); $st->execute(array(date('Y-m-d', strtotime('yesterday')))); $row = $st->fetch(); // no matches in cache if ($row[0] == 0) {     $st2 = $db->prepare('SELECT                           searchterm,                           source,                           FROM_DAYS(TO_DAYS(dt)) AS sdate,                           COUNT(*) as searches                        WHERE                           TO_DAYS(dt) = ?');    $st2->execute(array(date('Y-m-d', strtotime('yesterday'))));    $stInsert = $db->prepare('INSERT INTO searchsummary                              (searchterm,source,sdate,searches)                              VALUES (?,?,?,?)');    while ($row->fetch(PDO::FETCH_NUM)) {        $stInsert->execute($row);    } } ?>

Using this technique, your script will only incur the overhead of querying the full log table once, and all subsequent requests will retrieve a single row of summary data per search term.

11.7.4. See Also

Recipe 10.7 for information about PDO::prepare( ) and PDOStatement::execute( ) .

PHP Cookbook, 2nd Edition
PHP Cookbook: Solutions and Examples for PHP Programmers
ISBN: 0596101015
EAN: 2147483647
Year: 2006
Pages: 445

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: