1EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

1// Bad
2$users = $db->query('SELECT * FROM users');
3
4// Good
5$users = $db->query('SELECT id, name, email FROM users');

1// Always paginate
2$page = (int) ($_GET['page'] ?? 1);
3$perPage = 20;
4$offset = ($page - 1) * $perPage;
5
6$users = $db->query(
7    'SELECT id, name, email FROM users LIMIT ? OFFSET ?',
8    [$perPage, $offset]
9);

1CREATE INDEX idx_email ON users(email);

1CREATE INDEX idx_user_status ON users(status, created_at);

1CREATE INDEX idx_user_lookup ON users(email, name, status);
2
3-- This query uses only the index
4SELECT name, status FROM users WHERE email = 'john@example.com';

1// Bad - N+1 queries
2$posts = $db->query('SELECT * FROM posts');
3
4foreach ($posts as $post) {
5    // This runs a query for EACH post!
6    $author = $db->query('SELECT * FROM users WHERE id = ?', [$post['user_id']]);
7}

1// Good - Single query with JOIN
2$posts = $db->query('
3    SELECT 
4        p.id, p.title, p.content,
5        u.name as author_name, u.email as author_email
6    FROM posts p
7    INNER JOIN users u ON p.user_id = u.id
8');

1class CachedDatabase
2{
3    public function __construct(
4        private Database $db,
5        private Cache $cache
6    ) {}
7    
8    public function query(string $sql, array $params = [], int $ttl = 300): array
9    {
10        $cacheKey = 'query:' . md5($sql . serialize($params));
11        
12        $cached = $this->cache->get($cacheKey);
13        if ($cached !== null) {
14            return $cached;
15        }
16        
17        $result = $this->db->query($sql, $params);
18        $this->cache->set($cacheKey, $result, $ttl);
19        
20        return $result;
21    }
22}

1class UserRepository
2{
3    public function find(int $id): ?User
4    {
5        $cacheKey = "user:$id";
6        
7        $cached = $this->cache->get($cacheKey);
8        if ($cached) {
9            return $cached;
10        }
11        
12        $users = $this->db->query('SELECT * FROM users WHERE id = ?', [$id]);
13        
14        if (empty($users)) {
15            return null;
16        }
17        
18        $user = User::fromArray($users[0]);
19        $this->cache->set($cacheKey, $user, 3600);
20        
21        return $user;
22    }
23}

1class DatabasePool
2{
3    private static ?PDO $connection = null;
4    
5    public static function getConnection(): PDO
6    {
7        if (self::$connection === null) {
8            self::$connection = new PDO(
9                'mysql:host=localhost;dbname=myapp',
10                'user',
11                'pass',
12                [
13                    PDO::ATTR_PERSISTENT => true,
14                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
15                ]
16            );
17        }
18        
19        return self::$connection;
20    }
21}

1// Bad - Multiple queries
2foreach ($users as $user) {
3    $db->execute('INSERT INTO users (name, email) VALUES (?, ?)', 
4        [$user['name'], $user['email']]);
5}
6
7// Good - Single query
8$values = [];
9$params = [];
10
11foreach ($users as $user) {
12    $values[] = '(?, ?)';
13    $params[] = $user['name'];
14    $params[] = $user['email'];
15}
16
17$sql = 'INSERT INTO users (name, email) VALUES ' . implode(', ', $values);
18$db->execute($sql, $params);

1class ProfilingDatabase
2{
3    private array $queries = [];
4    
5    public function query(string $sql, array $params = []): array
6    {
7        $start = microtime(true);
8        $result = parent::query($sql, $params);
9        $duration = microtime(true) - $start;
10        
11        $this->queries[] = [
12            'sql' => $sql,
13            'params' => $params,
14            'duration' => $duration,
15            'rows' => count($result)
16        ];
17        
18        if ($duration > 0.1) {
19            error_log("Slow query ($duration s): $sql");
20        }
21        
22        return $result;
23    }
24    
25    public function getQueryLog(): array
26    {
27        return $this->queries;
28    }
29    
30    public function getTotalTime(): float
31    {
32        return array_sum(array_column($this->queries, 'duration'));
33    }
34}