Sumedicon Software Solutions
Sumedicon Software Solutions
Contact Us info@sumedicon.in
Backend

Optimizing PDO Database Connections in PHP 8.3

The PDO options we set on every PHP 8.3 project, the placeholder rule native prepares impose, when persistent connections hurt more than they help, and how to keep transactions and errors safe.

RS Rohan Sharma Systems Architect June 03, 2026 7 Min Read
Optimizing PDO Database Connections in PHP 8.3

Most PHP applications talk to their database through PDO, either directly or underneath an ORM. The defaults work, but a handful of connection options decide whether errors surface or vanish, whether prepared statements are real or simulated, and whether the database quietly runs out of connections under load. This is what we set on a new PHP 8.3 project, and why.

Start with the connection options

Four options cover almost every project. ERRMODE_EXCEPTION makes a failed query throw instead of returning false, so a broken statement cannot be mistaken for an empty result. A default fetch mode saves repeating it on every call. Turning off emulated prepares sends statements to MySQL as real server-side prepared statements, and the charset in the DSN makes sure the connection speaks utf8mb4 from the first byte.

php
<?php
$dsn = 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=utf8mb4';

$pdo = new PDO($dsn, DB_USER, DB_PASS, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

// One clock for PHP and MySQL, set per connection.
$pdo->exec("SET time_zone = '" . date('P') . "'");

Put the charset in the DSN rather than running SET NAMES afterwards. The DSN setting tells PDO which character set the connection uses, which matters for how values are escaped; SET NAMES changes the server side without telling the client library.

Real prepared statements have one surprise

With emulation off, a named placeholder can appear only once per statement. A query such as WHERE name LIKE :term OR email LIKE :term works with emulated prepares and fails with native ones, with SQLSTATE[HY093]: Invalid parameter number. The fix is to give each occurrence its own name and bind the value twice. It is a small cost for statements that MySQL parses once and types properly, so integers come back as integers rather than strings.

php
<?php
$stmt = $pdo->prepare(
    'SELECT id, name, email FROM leads
      WHERE name LIKE :term_name OR email LIKE :term_email'
);
$term = '%' . $search . '%';
$stmt->execute([':term_name' => $term, ':term_email' => $term]);

What prepared statements do not protect

Placeholders protect values. They cannot stand in for a table name, a column name or a sort direction, so code that builds ORDER BY from a query-string parameter is still open to injection however carefully the values are bound. Map anything structural through a whitelist: the request chooses a key, and the code chooses the SQL.

php
<?php
$sortable = ['newest' => 'created_at DESC', 'name' => 'name ASC'];
$orderBy  = $sortable[$_GET['sort'] ?? ''] ?? $sortable['newest'];

$rows = $pdo->query("SELECT id, name FROM leads ORDER BY $orderBy LIMIT 50")->fetchAll();

Persistent connections: measure before you enable them

ATTR_PERSISTENT keeps a connection open after the request ends so the next request served by the same PHP worker can reuse it, skipping the TCP and authentication handshake. On a database across a slow network that saving can be noticeable. On a database on the same host or in the same private network, it is usually a millisecond or less, and the costs are real.

Each PHP-FPM worker holds its own connection, so 50 workers on each of 4 web servers keep 200 connections open whether or not they are busy, and a traffic spike that adds workers can exhaust max_connections. A persistent connection also keeps its session state: a time_zone or sql_mode set by the last request, user variables, and, worst of all, a transaction that a failed request never committed or rolled back. The next request inherits it.

"Turn persistence on only when you have measured connection time as a real share of request time, and when every code path closes its transactions."

If you do enable it, size max_connections for the total number of workers, reset per-connection settings at the start of every request rather than relying on what the last one left, and watch the Threads_connected status variable after you deploy.

Use transactions for anything that must happen together

A transaction makes several statements succeed or fail as one: an order and its line items, a payment and the invoice it settles. With exceptions turned on, the pattern is short: roll back on any throwable, then rethrow, so the caller still sees the failure.

php
<?php
$pdo->beginTransaction();
try {
    $pdo->prepare('INSERT INTO orders (customer_id, total) VALUES (?, ?)')
        ->execute([$customerId, $total]);
    $orderId = (int)$pdo->lastInsertId();

    $line = $pdo->prepare('INSERT INTO order_items (order_id, sku, qty) VALUES (?, ?, ?)');
    foreach ($items as $item) {
        $line->execute([$orderId, $item['sku'], $item['qty']]);
    }
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

Two details trip people up. MySQL commits implicitly on DDL statements such as ALTER TABLE, so a schema change inside a transaction cannot be rolled back. And transactions only apply to InnoDB tables; a MyISAM table in the same block is written immediately.

Do not leak connection details in errors

A PDOException message can include the host name and the user the connection tried. Log it, and show the visitor a generic message. Wrapping it as new Exception('Database connection failed: ' . $e->getMessage()) and letting it reach the page is a common way configuration ends up on screen. Keep display_errors off in production, so an uncaught exception lands in the log file rather than the browser.

Cache what does not change, and invalidate it on write

Settings, menus and lookup tables are read on almost every request and change rarely. A cache such as Redis or APCu in front of them removes those queries entirely, but only if every write path also clears the cached copy. Cache the result of a specific query under a key that names it, give it an expiry as a backstop, and delete the key in the same code that updates the table.

A checklist for new projects

Exceptions on. Emulated prepares off, and a distinct name for every placeholder. utf8mb4 in the DSN. Whitelists for anything structural in SQL. Transactions around multi-statement writes, with a rollback in the catch. Persistent connections only after measuring. Error details in logs, never on the page. Caches cleared by the code that writes the data.

Need a second pair of eyes on a slow or fragile backend?

We review PHP and MySQL codebases and fix what we find, from connection handling to query plans.

Our software development service

Have a question about this?

Ask the team that wrote it. We reply within one business day, and there is no obligation.