-PHP Tips- 第4回 pgsql PEAR Cache

PEARのCacheを利用してPostgreSQLのクエリを高速化するには？
【Tips】PEARのCacheクラスを継承！
【Description】データベースの件数やSQLの複雑さに依存するので一概に言えないが、1万件のレコードをそのまま引っ張ってくるような場合で3倍程度高速です（シャア専用ってことです）。
Expireを上手に変えれば結構使える箇所は多いはず。日次でマスターが変更されるような場合3600秒のままでも問題なし。クエリの結果が巨大な場合はphpのメモリ上限を変更しないとダメなことも。以下のように使います。

$cache = new PGSQL_Query_Cache();
$cache->connect("DB_NAME", "DB_USER");
if($res = $cache->query("SELECT * FROM TABLE")){
    while($row = $cache->fetch_row()){
        echo($row[FLD_NAME]);
    }
}
<?php
require_once "Cache.php";

class PGSQL_Query_Cache extends Cache {
    var $connection = null;
    var $expires    = 3600;
    var $cursor = 0;
    var $result = array();

    function PGSQL_Query_Cache($container  = "file", $container_options = array("cache_dir"=> "/tmp/", "filename_prefix" => "cache_"), $expires = 3600){
        $this->Cache($container, $container_options);
        $this->expires = $expires;
    }

    function _PGSQL_Query_Cache() {
        if(is_resource($this->connection)){
            pg_close($this->connection);
        }
        $this->_Cache();
    }

    function connect($database, $username){
        $this->connection = pg_pconnect("dbname=".$database." user=".$username) or trigger_error('Could not connect to database.', E_USER_ERROR);
    }

    function fetch_row(){
        if($this->cursor < sizeof($this->result)){
            return $this->result[$this->cursor++];
        } else {
            return false;
        }
    }

    function num_rows(){
        return sizeof($this->result);
    }

    function query($query){
        if(stristr($query, 'SELECT')){
            $cache_id = md5($query);
            $this->result = $this->get($cache_id, 'pgsql_query_cache');
            if(!$this->result){
                $this->cursor = 0;
                $this->result = array();
                if(is_resource($this->connection)){
                    $result = pg_query($this->connection, $query);
                    while($row = pg_fetch_assoc($result)){
                        $this->result[] = $row;
                    }
                    pg_free_result($result);
                    $this->save($cache_id, $this->result, $this->expires, 'pgsql_query_cache');
                }
            }
            return $this->result;
        } else {
            return pg_query($this->connection, $query);
        }
    }
}
?>

関連