-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);
        }
    }
}
?>
</code

タイトルとURLをコピーしました