Designing Short Numeric Account IDs Using MySQL Auto‑Increment and Segment Allocation

This article explores the problem of generating short, sequential numeric account IDs, evaluates the classic Snowflake algorithm, proposes a MySQL auto‑increment based solution with a dedicated ID‑generation table, analyzes deadlock issues, and finally presents a segment‑based ID allocation scheme that reduces waste and supports scaling.

ID generationauto-incrementmysql

0 likes · 12 min read

Designing Short Numeric Account IDs Using MySQL Auto‑Increment and Segment Allocation

Liangxu Linux

May 29, 2023 · Databases

What Happens When MySQL Auto‑Increment IDs Reach Their Limits?

This article explains how MySQL handles overflow of various auto‑generated identifiers—including table auto_increment, InnoDB row_id, Xid, trx_id, and thread_id—detailing the wrap‑around behavior, potential data loss, and the rare edge cases that can lead to bugs.

InnoDBXidauto-increment

0 likes · 13 min read

What Happens When MySQL Auto‑Increment IDs Reach Their Limits?

Java Captain

Mar 21, 2021 · Databases

Performance Comparison of Auto‑Increment, UUID, and Random Snowflake IDs in MySQL

This article investigates why MySQL recommends auto_increment primary keys over UUID or non‑sequential snowflake IDs by building three tables, running insert‑and‑query benchmarks with Spring Boot's JdbcTemplate, analyzing index structures, and summarizing the performance trade‑offs and practical drawbacks of each key strategy.

auto-incrementindexingprimary-key

0 likes · 10 min read

Performance Comparison of Auto‑Increment, UUID, and Random Snowflake IDs in MySQL