Pairing with PHP/Python apps at scale – pooling, N+1

Pairing MySQL with PHP and Python apps at scale – connection pooling, N+1 queries, and habits that stop the database becoming the bottleneck. The schema can be perfect; the app layer still sends 500 queries per page.

Connection pooling

Each HTTP request that opens a new TCP connection to MySQL adds latency. Pooling reuses connections. PHP traditionally opens per request (PDO/MySQLi) – use persistent connections cautiously or a pooler in front of MySQL.

ProxySQL, PgBouncer-style poolers – sit between app servers and MySQL; multiplex many app connections to fewer DB connections
PHP-FPM – size worker count vs max_connections; 200 workers × 5 app servers can exhaust MySQL
Python – SQLAlchemy engine with pool_size and max_overflow; one engine per process, not per request

-- Check headroom
SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Connection_errors%';

Rule: total pooled connections across all app instances should stay below ~80% of max_connections, leaving room for admin, replicas, and backups.

Prepared statements everywhere

Both PHP and Python benefit from bound parameters – security and plan cache reuse. From Going further with PHP: PDO prepared statements, never string-concatenated user input.

-- App sends: SELECT * FROM products WHERE category_id = ? AND active = 1
-- One plan, many executions
PREPARE stmt FROM 'SELECT id, name, price FROM products WHERE category_id = ? AND active = 1';
SET @cat = 3;
EXECUTE stmt USING @cat;

The N+1 problem

Load 50 orders, then loop 50 times fetching each customer – 51 queries. Fix with JOIN or batch IN query.

-- N+1 pattern (app loop - bad)
SELECT id, customer_id, total FROM orders WHERE status = 'pending' LIMIT 50;
-- then per row: SELECT name FROM customers WHERE id = ?

-- One query with JOIN
SELECT o.id, o.total, c.name AS customer_name
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
LIMIT 50;

-- Or batch fetch customers after order IDs collected
SELECT id, name FROM customers WHERE id IN (12, 45, 67, ...);

ORMs make N+1 easy – enable query logging in dev, count queries per request, fix the worst pages first.

Read/write splitting

With replicas, configure two DSNs in app config – primary for writes, replica for reads. Laravel, Django, and custom apps need explicit routing or middleware; it is not automatic.

-- Write path (primary)
INSERT INTO audit_log (user_id, action, created_at) VALUES (1, 'login', NOW());

-- Read path (replica) - OK if slightly stale
SELECT COUNT(*) FROM audit_log WHERE created_at >= CURDATE();

Caching layers

Application cache (Redis, Memcached) – session, config, expensive aggregate for 60 seconds
Query result cache – invalidate on write; stale cache bugs are common, keep TTL short
MySQL query cache – removed in MySQL 8.0; do not chase it

Cache what is read often and changes rarely. Do not cache personalised pages without a key that includes user/tenant.

Batch writes and transactions

START TRANSACTION;

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1001, 10, 2, 19.99),
(1001, 11, 1, 4.50),
(1001, 15, 3, 2.00);

UPDATE orders SET item_count = 3, subtotal = 52.48 WHERE id = 1001;

COMMIT;

Multi-row INSERT beats 100 single-row INSERTs in a loop from PHP/Python. Wrap related changes in one transaction – see the intermediate transactions tutorial.

Pagination at scale

-- OFFSET pagination gets slow on deep pages
SELECT id, title FROM posts ORDER BY id DESC LIMIT 20 OFFSET 100000;

-- Keyset (seek) pagination
SELECT id, title FROM posts
WHERE id < 980001
ORDER BY id DESC
LIMIT 20;

Use keyset pagination for infinite scroll on large tables. OFFSET forces MySQL to scan and discard rows.

The next tutorial is a mini project: optimise a deliberately bad schema and query set.