mysqlnd-qc-php-quickstart-php-caching-4

  • Quickstart and
    Examples
  • Caching queries

  • Caching queries
  • Caching queries

    Caching queries

    There are four ways to trigger caching of a
    query.

    • Use of SQL hints on a
      per query basis
    • User supplied callbacks
      to decide on a per query basis, for example, using mysqlnd_qc_is_select()
    • mysqlnd_set_cache_condition()
      for rule based automatic per query decisions
    • mysqlnd_qc.cache_by_default = 1 to cache all
      queries blindly

    Use of SQL hints and mysqlnd_qc.cache_by_default = 1 are explained
    below. Please, refer to the function reference on mysqlnd_qc_is_select() for a
    description of using a callback and, mysqlnd_qc_set_cache_condition() on how
    to set rules for automatic caching.

    A SQL hint is a SQL standards compliant comment. As
    a SQL comment it is ignored by the database. A statement is
    considered eligible for caching if it either begins with the SQL
    hint enabling caching or it is a SELECT statement.

    An individual query which shall be cached must
    begin with the SQL hint /*qc=on*/. It is recommended to
    use the PHP constant MYSQLND_QC_ENABLE_SWITCH instead of using the
    string value.

    • not eligible for caching and not cached: INSERT
      INTO test(id) VALUES (1)

    • not eligible for caching and not cached: SHOW
      ENGINES

    • eligible for caching but uncached: SELECT id
      FROM test

    • eligible for caching and cached:
      /*qc=on*/SELECT id FROM test

    The examples SELECT statement string is
    prefixed with the MYSQLND_QC_ENABLE_SWITCH SQL hint to enable caching
    of the statement. The SQL hint must be given at the very beginning
    of the statement string to enable caching.

    Example #1 Using the MYSQLND_QC_ENABLE_SWITCH
    SQL hint

    mysqlnd_qc.enable_qc=1
    
    <?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 INT)");
    $mysqli->query("INSERT INTO test(id) VALUES (1), (2)");

    /* Will be cached because of the SQL hint */
    $start microtime(true);
    $res   $mysqli->query("/*" MYSQLND_QC_ENABLE_SWITCH "*/" "SELECT id FROM test WHERE id = 1");

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

    printf("Total time uncached query: %.6fs\n"microtime(true) - $start);

    /* Cache hit */
    $start microtime(true);
    $res   $mysqli->query("/*" MYSQLND_QC_ENABLE_SWITCH "*/" "SELECT id FROM test WHERE id = 1");

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

    printf("Total time cached query: %.6fs\n"microtime(true) - $start);
    ?>

    The above examples will output something similar to:

    array(1) {
      ["id"]=>
      string(1) "1"
    }
    Total time uncached query: 0.000740s
    array(1) {
      ["id"]=>
      string(1) "1"
    }
    Total time cached query: 0.000098s
    

    If nothing else is configured, as it is the case in
    the quickstart example, the plugin will use the built-in
    default storage handler. The default storage
    handler uses process memory to hold a cache entry. Depending on the
    PHP deployment model, a PHP process may serve one or more web
    requests. Please, consult the web server manual for details.
    Details make no difference for the examples given in the
    quickstart.

    The query cache plugin will cache all queries
    regardless if the query string begins with the SQL hint which
    enables caching or not, if the PHP configuration directive
    mysqlnd_qc.cache_by_default is set to 1.
    The setting mysqlnd_qc.cache_by_default is evaluated by the
    core of the query cache plugins. Neither the built-in nor
    user-defined storage handler can overrule the setting.

    The SQL hint /*qc=off*/ can be used to
    disable caching of individual queries if mysqlnd_qc.cache_by_default = 1 It is recommended
    to use the PHP constant MYSQLND_QC_DISABLE_SWITCH instead of using
    the string value.

    Example #2 Using the MYSQLND_QC_DISABLE_SWITCH
    SQL hint

    mysqlnd_qc.enable_qc=1
    mysqlnd_qc.cache_by_default=1
    
    <?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 INT)");
    $mysqli->query("INSERT INTO test(id) VALUES (1), (2)");

    /* Will be cached although no SQL hint is present because of mysqlnd_qc.cache_by_default = 1*/
    $res $mysqli->query("SELECT id FROM test WHERE id = 1");
    var_dump($res->fetch_assoc());
    $res->free();

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

    /* Cache hit - no automatic invalidation and still valid! */
    $res $mysqli->query("SELECT id FROM test WHERE id = 1");
    var_dump($res->fetch_assoc());
    $res->free();

    /* Cache miss - query must not be cached because of the SQL hint */
    $res $mysqli->query("/*" MYSQLND_QC_DISABLE_SWITCH "*/SELECT id FROM test WHERE id = 1");
    var_dump($res->fetch_assoc());
    $res->free();
    ?>

    The above examples will output:

    array(1) {
      ["id"]=>
      string(1) "1"
    }
    array(1) {
      ["id"]=>
      string(1) "1"
    }
    NULL
    

    PECL/mysqlnd_qc forbids caching of statements for
    which at least one column from the statements result set shows no
    table name in its meta data by default. This is usually the case
    for columns originating from SQL functions such as NOW()
    or LAST_INSERT_ID(). The policy aims to prevent pitfalls
    if caching by default is used.

    Example #3 Example showing which type of statements are
    not cached

    mysqlnd_qc.enable_qc=1
    mysqlnd_qc.cache_by_default=1
    
    <?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 INT)");
    $mysqli->query("INSERT INTO test(id) VALUES (1)");

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

        $start microtime(true);

        /* Note: statement will not be cached because of NOW() use */
        
    $res $mysqli->query("SELECT id, NOW() AS _time FROM test");
        
    $row $res->fetch_assoc();

        /* dump results */
        
    var_dump($row);

        printf("Total time: %.6fs\n"microtime(true) - $start);

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

    The above examples will output something similar to:

    array(2) {
      ["id"]=>
      string(1) "1"
      ["_time"]=>
      string(19) "2012-01-11 15:43:10"
    }
    Total time: 0.000540s
    array(2) {
      ["id"]=>
      string(1) "1"
      ["_time"]=>
      string(19) "2012-01-11 15:43:11"
    }
    Total time: 0.000555s
    array(2) {
      ["id"]=>
      string(1) "1"
      ["_time"]=>
      string(19) "2012-01-11 15:43:12"
    }
    Total time: 0.000549s
    

    It is possible to enable caching for all statements
    including those which has columns in their result set for which
    MySQL reports no table, such as the statement from the example. Set
    mysqlnd_qc.cache_no_table = 1 to enable
    caching of such statements. Please, note the difference in the
    measured times for the above and below examples.

    Example #4 Enabling caching for all statements using the
    mysqlnd_qc.cache_no_table ini setting

    mysqlnd_qc.enable_qc=1
    mysqlnd_qc.cache_by_default=1
    mysqlnd_qc.cache_no_table=1
    
    <?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 INT)");
    $mysqli->query("INSERT INTO test(id) VALUES (1)");

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

        $start microtime(true);

        /* Note: statement will not be cached because of NOW() use */
        
    $res $mysqli->query("SELECT id, NOW() AS _time FROM test");
        
    $row $res->fetch_assoc();

        /* dump results */
        
    var_dump($row);

        printf("Total time: %.6fs\n"microtime(true) - $start);

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

    The above examples will output something similar to:

    array(2) {
      ["id"]=>
      string(1) "1"
      ["_time"]=>
      string(19) "2012-01-11 15:47:45"
    }
    Total time: 0.000546s
    array(2) {
      ["id"]=>
      string(1) "1"
      ["_time"]=>
      string(19) "2012-01-11 15:47:45"
    }
    Total time: 0.000187s
    array(2) {
      ["id"]=>
      string(1) "1"
      ["_time"]=>
      string(19) "2012-01-11 15:47:45"
    }
    Total time: 0.000167s
    

    Note:

    Although mysqlnd_qc.cache_no_table = 1 has been
    created for use with mysqlnd_qc.cache_by_default = 1 it is bound
    it. The plugin will evaluate the mysqlnd_qc.cache_no_table whenever a
    query is to be cached, no matter whether caching has been enabled
    using a SQL hint or any other measure.