Skip to content

09-query-caching.md

Latest commit

 

History

History
722 lines (557 loc) · 16.4 KB

09-query-caching.md

File metadata and controls

722 lines (557 loc) · 16.4 KB

Query Caching

PDOdb provides built-in support for query result caching using PSR-16 (Simple Cache) compatible implementations. This feature can significantly improve application performance by reducing database load and query execution time.

Table of Contents

Overview

Query caching stores the results of SELECT queries in a cache layer, allowing subsequent identical queries to retrieve results from cache instead of executing against the database.

Benefits

  • Reduced Database Load: Fewer queries executed
  • Faster Response Times: Cache reads are typically 10-100x faster than database queries
  • Scalability: Better handling of high-traffic scenarios
  • Cost Savings: Lower database server resource usage

When to Use Caching

✅ Good candidates for caching:

  • Configuration and settings
  • Product catalogs
  • User profiles (with appropriate TTL)
  • Aggregate reports and statistics
  • Static or rarely-changing content
  • Expensive JOIN queries
  • Complex aggregations

❌ Poor candidates for caching:

  • Real-time data (stock prices, live scores)
  • Frequently updated records
  • User-specific data with high variability
  • Write-heavy tables
  • Data requiring strong consistency

Setup

1. Install a PSR-16 Cache Implementation

# Symfony Cache (recommended - provides multiple adapters)
composer require symfony/cache

# Optional: For APCu cache
# Enable ext-apcu extension in php.ini

# Optional: For Redis cache
# Install ext-redis extension

# Optional: For Memcached cache
# Install ext-memcached extension

2. Create Cache Instance

Option A: Using CacheFactory (Recommended)

CacheFactory provides a convenient way to create cache adapters from configuration:

use tommyknocker\pdodb\cache\CacheFactory;
use tommyknocker\pdodb\PdoDb;

// Filesystem cache
$cache = CacheFactory::create([
    'type' => 'filesystem',
    'directory' => '/var/cache/pdodb',
    'namespace' => 'app',
    'default_lifetime' => 3600,
]);

// Redis cache
$cache = CacheFactory::create([
    'type' => 'redis',
    'host' => '127.0.0.1',
    'port' => 6379,
    'database' => 0,
    'namespace' => 'app',
]);

// APCu cache
$cache = CacheFactory::create([
    'type' => 'apcu',
    'namespace' => 'app',
]);

// Pass cache to PdoDb
$db = new PdoDb(
    'mysql',
    [
        'host' => 'localhost',
        'dbname' => 'myapp',
        'username' => 'user',
        'password' => 'pass',
    ],
    [],
    null,
    $cache
);

Option B: Manual Creation

use Symfony\Component\Cache\Adapter\FilesystemAdapter;
use Symfony\Component\Cache\Psr16Cache;
use tommyknocker\pdodb\PdoDb;

// Create PSR-16 cache
$adapter = new FilesystemAdapter();
$cache = new Psr16Cache($adapter);

// Pass cache to PdoDb constructor
$db = new PdoDb(
    'mysql',
    [
        'host' => 'localhost',
        'dbname' => 'myapp',
        'username' => 'user',
        'password' => 'pass',
    ],
    [],
    null,
    $cache  // PSR-16 cache instance
);

3. Optional: Configure Cache Behavior

$db = new PdoDb(
    'mysql',
    [
        'host' => 'localhost',
        'dbname' => 'myapp',
        'username' => 'user',
        'password' => 'pass',
        'cache' => [
            'prefix' => 'myapp_',        // Cache key prefix
            'default_ttl' => 7200,       // Default TTL (seconds)
            'enabled' => true,           // Global enable/disable
        ],
    ],
    [],
    null,
    $cache
);

Basic Usage

Enable Caching for a Query

Use the cache() method to enable caching for a specific query:

// Cache for 1 hour (3600 seconds)
$users = $db->find()
    ->from('users')
    ->where('active', 1)
    ->cache(3600)
    ->get();

Custom Cache Key

Provide a custom key for easier cache management:

$settings = $db->find()
    ->from('settings')
    ->cache(3600, 'app_settings')
    ->get();

Disable Caching

Use noCache() to explicitly disable caching:

$data = $db->find()
    ->from('products')
    ->cache(3600)  // Enable cache
    ->noCache()    // But then disable it
    ->get();       // This query won't be cached

Cache Methods

cache(int $ttl, ?string $key = null)

Enable caching for the current query.

Parameters:

  • $ttl - Time-to-live in seconds
  • $key - Optional custom cache key (auto-generated if null)

Returns: self (for method chaining)

// Auto-generated key
$result = $db->find()
    ->from('products')
    ->where('category', 'Electronics')
    ->cache(1800)  // 30 minutes
    ->get();

// Custom key
$result = $db->find()
    ->from('products')
    ->where('featured', 1)
    ->cache(3600, 'featured_products')
    ->get();

noCache()

Disable caching for the current query.

Returns: self (for method chaining)

$result = $db->find()
    ->from('users')
    ->noCache()
    ->get();

Works With All Fetch Methods

Caching works seamlessly with all result-fetching methods:

get() - Fetch All Rows

$products = $db->find()
    ->from('products')
    ->where('stock', 0, '>')
    ->cache(600)
    ->get();

getOne() - Fetch Single Row

$user = $db->find()
    ->from('users')
    ->where('email', $email)
    ->cache(1800)
    ->getOne();

getValue() - Fetch Single Value

$count = $db->find()
    ->from('orders')
    ->select([Db::count('*', 'total')])
    ->where('status', 'pending')
    ->cache(300)
    ->getValue();

getColumn() - Fetch Column Values

$usernames = $db->find()
    ->from('users')
    ->select('username')
    ->where('active', 1)
    ->cache(600)
    ->getColumn();

Cache Configuration

Configuration Options

Option Type Default Description
prefix string 'pdodb_' Cache key prefix for namespacing
default_ttl int 3600 Default TTL in seconds
enabled bool true Global cache enable/disable

Example Configuration

$config = [
    'host' => 'localhost',
    'dbname' => 'myapp',
    'username' => 'user',
    'password' => 'pass',
    'cache' => [
        'prefix' => 'myapp_v1_',
        'default_ttl' => 7200,
        'enabled' => true,
    ],
];

$db = new PdoDb('mysql', $config, [], null, $cache);

Accessing Configuration

$cacheManager = $db->getCacheManager();
$config = $cacheManager->getConfig();

echo "Prefix: " . $config->getPrefix() . "\n";
echo "Default TTL: " . $config->getDefaultTtl() . " seconds\n";
echo "Enabled: " . ($config->isEnabled() ? 'Yes' : 'No') . "\n";

Cache Key Generation

Auto-Generated Keys

When no custom key is provided, cache keys are automatically generated using:

  • SQL query string
  • Query parameters
  • Database driver name

This ensures different queries (or same query with different parameters) get different cache entries.

// These create different cache entries
$result1 = $db->find()->from('users')->where('age', 25, '>')->cache(3600)->get();
$result2 = $db->find()->from('users')->where('age', 30, '>')->cache(3600)->get();

Custom Keys

Custom keys are useful for:

  • Easier cache invalidation
  • Shared caching logic
  • Debugging and monitoring
// Set cache with custom key
$products = $db->find()
    ->from('products')
    ->where('featured', 1)
    ->cache(3600, 'featured_products')
    ->get();

// Later, invalidate if needed
$cacheManager = $db->getCacheManager();
$cacheManager->delete('featured_products');

Key Prefix

All cache keys are automatically prefixed based on configuration:

// With prefix 'myapp_'
$cache->cache(3600, 'users');
// Actual key: 'myapp_users'

// Auto-generated keys also get prefix
$cache->cache(3600);
// Actual key: 'myapp_[hash]'

PSR-16 Implementations

PDOdb works with any PSR-16 compatible cache. Here are recommended implementations:

1. Using CacheFactory (Recommended)

CacheFactory provides a unified interface for creating cache adapters from optional dependencies:

use tommyknocker\pdodb\cache\CacheFactory;

// Filesystem cache (requires symfony/cache)
$cache = CacheFactory::create([
    'type' => 'filesystem',
    'directory' => '/var/cache/pdodb',
    'namespace' => 'myapp',
    'default_lifetime' => 3600,
]);

// Redis cache (requires ext-redis and symfony/cache)
$cache = CacheFactory::create([
    'type' => 'redis',
    'host' => '127.0.0.1',
    'port' => 6379,
    'database' => 0,
    'password' => null, // Optional
    'namespace' => 'myapp',
]);

// APCu cache (requires ext-apcu and symfony/cache)
$cache = CacheFactory::create([
    'type' => 'apcu',
    'namespace' => 'myapp',
]);

// Memcached cache (requires ext-memcached and symfony/cache)
$cache = CacheFactory::create([
    'type' => 'memcached',
    'servers' => [['127.0.0.1', 11211]],
    'namespace' => 'myapp',
]);

Note: CacheFactory::create() returns null if the required dependencies are not installed, so always check:

$cache = CacheFactory::create($config);
if ($cache === null) {
    // Handle missing dependencies
    throw new \RuntimeException('Cache adapter not available');
}

2. Manual Creation with Symfony Cache

You can also create cache adapters manually:

Filesystem Cache:

use Symfony\Component\Cache\Adapter\FilesystemAdapter;
use Symfony\Component\Cache\Psr16Cache;

$adapter = new FilesystemAdapter(
    namespace: 'myapp',
    defaultLifetime: 0,
    directory: '/tmp/cache'
);
$cache = new Psr16Cache($adapter);

Redis Cache:

use Symfony\Component\Cache\Adapter\RedisAdapter;
use Symfony\Component\Cache\Psr16Cache;

$redis = RedisAdapter::createConnection('redis://localhost:6379');
$adapter = new RedisAdapter($redis);
$cache = new Psr16Cache($adapter);

APCu Cache (High Performance):

use Symfony\Component\Cache\Adapter\ApcuAdapter;
use Symfony\Component\Cache\Psr16Cache;

$adapter = new ApcuAdapter(namespace: 'myapp');
$cache = new Psr16Cache($adapter);

Memcached Cache:

use Symfony\Component\Cache\Adapter\MemcachedAdapter;
use Symfony\Component\Cache\Psr16Cache;

$client = MemcachedAdapter::createConnection('memcached://localhost:11211');
$adapter = new MemcachedAdapter($client);
$cache = new Psr16Cache($adapter);

3. Array Cache (Testing)

For unit tests and development:

use tommyknocker\pdodb\tests\fixtures\ArrayCache;

$cache = new ArrayCache();
$db = new PdoDb('sqlite', ['path' => ':memory:'], [], null, $cache);

Or use CacheFactory:

use tommyknocker\pdodb\cache\CacheFactory;

$cache = CacheFactory::create(['type' => 'array']);

Best Practices

1. Choose Appropriate TTL

// Short TTL for frequently changing data
$recentOrders = $db->find()
    ->from('orders')
    ->where('created_at', date('Y-m-d'), '>')
    ->cache(300)  // 5 minutes
    ->get();

// Long TTL for static data
$categories = $db->find()
    ->from('categories')
    ->cache(86400)  // 24 hours
    ->get();

// Very long TTL for configuration
$settings = $db->find()
    ->from('app_settings')
    ->cache(604800, 'app_settings')  // 1 week
    ->get();

2. Use Custom Keys for Important Data

// Easy to invalidate later
$featuredProducts = $db->find()
    ->from('products')
    ->where('featured', 1)
    ->cache(3600, 'featured_products')
    ->get();

// When product is updated
$db->find()->table('products')->where('id', $productId)->update(['featured' => 1]);
$db->getCacheManager()->delete('featured_products');

3. Cache Expensive Queries

// Complex aggregation - good candidate for caching
$report = $db->find()
    ->from('orders')
    ->select([
        'date' => Db::date('created_at'),
        Db::count('*', 'total_orders'),
        Db::sum('total_amount', 'revenue'),
    ])
    ->where('created_at', date('Y-m-d', strtotime('-30 days')), '>=')
    ->groupBy('date')
    ->cache(3600, 'monthly_report')
    ->get();

4. Conditional Caching

function getProducts($category, $useCache = true) {
    $query = $db->find()
        ->from('products')
        ->where('category', $category);
    
    if ($useCache) {
        $query->cache(1800);
    }
    
    return $query->get();
}

// Development: no cache
$products = getProducts('Electronics', false);

// Production: use cache
$products = getProducts('Electronics', true);

5. Monitor Cache Performance

// Track cache hits/misses
$cacheManager = $db->getCacheManager();
$startTime = microtime(true);

$result = $db->find()
    ->from('products')
    ->cache(3600, 'all_products')
    ->get();

$duration = microtime(true) - $startTime;
error_log("Query duration: {$duration}s");

Performance Considerations

Cache vs Database Performance

Operation Database Cache (Memory) Cache (Redis) Speedup
Simple SELECT 5-10ms 0.1-0.5ms 1-2ms 5-100x
Complex JOIN 50-500ms 0.1-0.5ms 1-2ms 50-500x
Aggregation 100-1000ms 0.1-0.5ms 1-2ms 100-1000x

Memory Considerations

Cache Size Estimation:

Average Row Size: 1 KB
Rows per Query: 100
Queries Cached: 1000
Total Memory: 100 MB

Recommendations:

  • Monitor cache size in production
  • Set appropriate TTL to limit growth
  • Use Redis/Memcached for large datasets
  • Clear cache periodically

Network Overhead

Local Cache (APCu, File):

  • Latency: <0.1ms
  • Best for single-server deployments

Remote Cache (Redis, Memcached):

  • Latency: 1-5ms
  • Best for multi-server deployments
  • Adds network round-trip time

Troubleshooting

Cache Not Working

Check if cache is enabled:

$cacheManager = $db->getCacheManager();
if ($cacheManager === null) {
    echo "No cache manager configured\n";
} else {
    $config = $cacheManager->getConfig();
    echo "Cache enabled: " . ($config->isEnabled() ? 'Yes' : 'No') . "\n";
}

Verify cache() is called:

// This won't cache
$result = $db->find()->from('users')->get();

// This will cache
$result = $db->find()->from('users')->cache(3600)->get();

Stale Data

Symptoms: Cached data doesn't reflect recent updates

Solutions:

  1. Reduce TTL:
$data = $db->find()->from('products')->cache(300)->get(); // 5 minutes instead of 1 hour
  1. Manual Invalidation:
// After update
$db->find()->table('products')->where('id', $id)->update($data);
$db->getCacheManager()->delete('product_list');
  1. Disable cache for writes:
// Always fetch fresh after write
$db->find()->table('products')->where('id', $id)->update($data);
$product = $db->find()->from('products')->where('id', $id)->noCache()->getOne();

Memory Issues

Symptoms: Out of memory errors, slow cache performance

Solutions:

  1. Use distributed cache (Redis, Memcached)
  2. Reduce cache TTL
  3. Limit cached result size:
// Don't cache huge result sets
$query = $db->find()->from('logs')->limit(1000);
if ($query->limit <= 100) {
    $query->cache(600);
}
$results = $query->get();

Different Results from Cache

Symptoms: Cached results differ from database

Cause: Parameters or query changed, but same cache key used

Solution: Use unique cache keys or rely on auto-generation:

// Bad - reuses same key
$result1 = $db->find()->from('users')->where('age', 25, '>')->cache(3600, 'users')->get();
$result2 = $db->find()->from('users')->where('age', 30, '>')->cache(3600, 'users')->get();

// Good - different keys
$result1 = $db->find()->from('users')->where('age', 25, '>')->cache(3600, 'users_over_25')->get();
$result2 = $db->find()->from('users')->where('age', 30, '>')->cache(3600, 'users_over_30')->get();

// Best - auto-generated keys
$result1 = $db->find()->from('users')->where('age', 25, '>')->cache(3600)->get();
$result2 = $db->find()->from('users')->where('age', 30, '>')->cache(3600)->get();

Examples

See Query Caching examples for complete working examples.

Related Documentation