Q: What is the difference between MySQL and MariaDB upsert syntax?

MySQL supports row aliases like VALUES (...) AS new for ON DUPLICATE KEY UPDATE and deprecates VALUES(col), while MariaDB supports VALUES(col) as the standard form. Use VALUES(col) for cross-engine compatibility in mixed deployments.

Q: When should I use SKIP LOCKED in MySQL?

Use SKIP LOCKED only for queue-style workloads where workers claim pending jobs and skipping locked rows is acceptable. It can return inconsistent views, so never use it for accounting reads or integrity-sensitive queries.

Q: Why does my application get stale reads after writes?

Read replicas lag behind the primary, so read-your-writes flows, checkout, and permission checks routed to replicas may see old data. Pin these paths to the primary and monitor replica SQL thread health and lag.

Q: How do I configure a MySQL connection pool in SQLAlchemy?

Set pool_size, max_overflow, pool_timeout, and pool_recycle below the server's wait_timeout (for example 240 seconds when wait_timeout is 300), and enable pool_pre_ping to recover from stale connections after network failures or failovers.

Question 1

How do I design indexes for MySQL queries?

Accepted Answer

Place equality predicate columns first in composite indexes, followed by range or sort columns, such as (account_id, status, created_at). Always run EXPLAIN before adding indexes and watch for type=ALL, NULL key, or Using filesort signals.

Question 2

How to implement pagination in MySQL without slow OFFSET?

Accepted Answer

Use keyset pagination: filter with WHERE (created_at, id) < (?, ?) and order by the same columns, backed by a matching composite index. Deep OFFSET forces the server to scan and discard rows, slowing linearly with page depth.

Question 3

What is the difference between MySQL and MariaDB upsert syntax?

Accepted Answer

MySQL supports row aliases like VALUES (...) AS new for ON DUPLICATE KEY UPDATE and deprecates VALUES(col), while MariaDB supports VALUES(col) as the standard form. Use VALUES(col) for cross-engine compatibility in mixed deployments.

Question 4

When should I use SKIP LOCKED in MySQL?

Accepted Answer

Use SKIP LOCKED only for queue-style workloads where workers claim pending jobs and skipping locked rows is acceptable. It can return inconsistent views, so never use it for accounting reads or integrity-sensitive queries.

Question 5

Why does my application get stale reads after writes?

Accepted Answer

Read replicas lag behind the primary, so read-your-writes flows, checkout, and permission checks routed to replicas may see old data. Pin these paths to the primary and monitor replica SQL thread health and lag.

Question 6

How do I configure a MySQL connection pool in SQLAlchemy?

Accepted Answer

Set pool_size, max_overflow, pool_timeout, and pool_recycle below the server's wait_timeout (for example 240 seconds when wait_timeout is 300), and enable pool_pre_ping to recover from stale connections after network failures or failovers.

dev-mysql

SYSTEM DOCUMENTATION & REQUIREMENTS

Frequently Asked Questions about dev-mysql

Related Skills

capacity-planning-helper

engineering-database-optimizer

fullstack-gcp-engineer

postgresql-table-design