mysqlnd-qc-php-per-query-ttl-1

  • Quickstart and
    Examples
  • Setting the TTL

  • Setting the TTL
  • Setting the TTL

    Setting the TTL

    The default invalidation strategy of the query
    cache plugin is Time to Live (TTL). The built-in storage
    handlers will use the default TTL defined by the PHP
    configuration value mysqlnd_qc.ttl unless the query string
    contains a hint for setting a different TTL. The
    TTL is specified in seconds. By default cache entries
    expire after 30 seconds

    The example sets mysqlnd_qc.ttl=3 to cache
    statements for three seconds by default. Every second it updates a
    database table record to hold the current time and executes a
    SELECT statement to fetch the record from the database.
    The SELECT statement is cached for three seconds because
    it is prefixed with the SQL hint enabling caching. The output
    verifies that the query results are taken from the cache for the
    duration of three seconds before they are refreshed.

    Example #1 Setting the TTL with the
    mysqlnd_qc.ttl ini setting

    mysqlnd_qc.enable_qc=1
    mysqlnd_qc.ttl=3
    
    <?php
    /* Connect, create and populate test table */
    $mysqli = new mysqli("host""user""password""schema""port""socket");
    $mysqli->query("DROP TABLE IF EXISTS test");
    $mysqli->query("CREATE TABLE test(id VARCHAR(255))");

    for ($i 0$i 7$i++) {

        /* update DB row  */
        
    if (!$mysqli->query("DELETE FROM test") ||
            !
    $mysqli->query("INSERT INTO test(id) VALUES (NOW())"))
          
    /* Of course, a real-life script should do better error handling */
          
    die(sprintf("[%d] %s\n"$mysqli->errno$mysqli->error));

        /* select latest row but cache results */
        
    $query  "/*" MYSQLND_QC_ENABLE_SWITCH "*/";
        
    $query .= "SELECT id AS _time FROM test";
        if (!(
    $res $mysqli->query($query)) ||
            !(
    $row $res->fetch_assoc()))
        {
          
    printf("[%d] %s\n"$mysqli->errno$mysqli->error);
        }
        
    $res->free();
        
    printf("Wall time %s - DB row time %s\n"date("H:i:s"), $row['_time']);

        /* pause one second */
        
    sleep(1);
    }
    ?>

    The above examples will output something similar to:

    Wall time 14:55:59 - DB row time 2012-01-11 14:55:59
    Wall time 14:56:00 - DB row time 2012-01-11 14:55:59
    Wall time 14:56:01 - DB row time 2012-01-11 14:55:59
    Wall time 14:56:02 - DB row time 2012-01-11 14:56:02
    Wall time 14:56:03 - DB row time 2012-01-11 14:56:02
    Wall time 14:56:04 - DB row time 2012-01-11 14:56:02
    Wall time 14:56:05 - DB row time 2012-01-11 14:56:05
    

    As can be seen from the example, any TTL
    based cache can serve stale data. Cache entries are not
    automatically invalidated, if underlying data changes. Applications
    using the default TTL invalidation strategy must be able
    to work correctly with stale data.

    A user-defined cache storage handler can implement
    any invalidation strategy to work around this limitation.

    The default TTL can be overruled using the
    SQL hint /*qc_tt=seconds*/. The SQL hint must be appear
    immediately after the SQL hint which enables caching. It is
    recommended to use the PHP constant MYSQLND_QC_TTL_SWITCH instead of using the string
    value.

    Example #2 Setting TTL with SQL hints

    <?php
    $start 
    microtime(true);

    /* Connect, create and populate test table */
    $mysqli = new mysqli("host""user""password""schema""port""socket");
    $mysqli->query("DROP TABLE IF EXISTS test");
    $mysqli->query("CREATE TABLE test(id INT)");
    $mysqli->query("INSERT INTO test(id) VALUES (1), (2)");

    printf("Default TTL\t: %d seconds\n"ini_get("mysqlnd_qc.ttl"));

    /* Will be cached for 2 seconds */
    $sql sprintf("/*%s*//*%s%d*/SELECT id FROM test WHERE id = 1"MYSQLND_QC_ENABLE_SWITCHMYSQLND_QC_TTL_SWITCH2);
    $res $mysqli->query($sql);

    var_dump($res->fetch_assoc());
    $res->free();

    $mysqli->query("DELETE FROM test WHERE id = 1");
    sleep(1);

    /* Cache hit - no automatic invalidation and still valid! */
    $res $mysqli->query($sql);
    var_dump($res->fetch_assoc());
    $res->free();

    sleep(2);

    /* Cache miss - cache entry has expired */
    $res $mysqli->query($sql);
    var_dump($res->fetch_assoc());
    $res->free();

    printf("Script runtime\t: %d seconds\n"microtime(true) - $start);
    ?>

    The above examples will output something similar to:

    Default TTL     : 30 seconds
    array(1) {
      ["id"]=>
      string(1) "1"
    }
    array(1) {
      ["id"]=>
      string(1) "1"
    }
    NULL
    Script runtime  : 3 seconds