1 views collected around this technical thread.
The article explains why using a VARCHAR(20) column to store billions of phone numbers is safer and more flexible than INT or BIGINT, covering range limits, data integrity, query convenience, interview expectations, and common pitfalls such as insufficient field length, charset issues, missing indexes, and lack of encryption.
The article examines an interview question about storing 2 billion phone numbers, explains why int cannot hold 11‑digit numbers, advocates using VARCHAR(20) for flexibility and data integrity, and outlines common pitfalls and best practices for database design.
When designing a schema for two‑billion phone numbers, use a VARCHAR(20) column with utf8mb4 Unicode, a unique index, and proper validation/encryption rather than a 32‑bit INT, because strings preserve leading zeros, international prefixes, extensions, and support business extensibility, fault tolerance, and future changes.
This article experimentally investigates whether the length of VARCHAR columns (e.g., 50 vs 500) affects MySQL storage size and query performance, covering table creation, bulk data insertion, storage queries, index and full‑table scans, and explains the underlying reasons for any differences observed.
This article investigates whether the declared length of VARCHAR columns (e.g., VARCHAR(50) vs VARCHAR(500)) affects MySQL storage size and query performance by creating two tables, inserting one million rows, measuring disk usage, and benchmarking various SELECT and ORDER BY operations.
Testing shows that VARCHAR(50) and VARCHAR(500) occupy identical storage, yet while simple lookups perform similarly, sorting on the longer column triggers disk‑based mergesort and can be several times slower, demonstrating that excessive VARCHAR length harms query performance without saving space.
The article investigates why altering a MySQL VARCHAR column from a length that fits within 255 bytes to one that exceeds this limit (e.g., VARCHAR(63) to VARCHAR(64) in utf8mb4) triggers a costly copy operation, analyzes the storage mechanics, reproduces the issue, and provides practical recommendations to avoid performance penalties.
This article analyzes why ALTER TABLE operations that extend VARCHAR columns sometimes fail in MySQL, explains the underlying Online DDL constraints, presents several real‑world problems such as default‑value handling, indexed columns, and hidden table‑rebuild quirks, and offers practical solutions for each case.
The article explains why storing IPv4 addresses as VARCHAR is suboptimal, demonstrates that a 32‑bit unsigned INT perfectly fits an IPv4 address, compares storage size and query performance, and shows how to use MySQL's inet_aton and inet_ntoa conversion functions.
This article explains how InnoDB stores data on disk, the role of 16 KB pages in read/write operations, the dynamic row format’s length and NULL lists, overflow handling, and why varchar columns are limited to 16 383 characters under utf8mb4, illustrated with SQL examples.
This article explains how InnoDB stores data on disk using 16 KB pages, describes the dynamic row format and its handling of variable‑length columns such as VARCHAR, and clarifies why MySQL limits VARCHAR to a maximum of 16 383 characters under utf8mb4.
The article explains that using a 32‑bit UNSIGNED INT to store IPv4 addresses in MySQL saves space, improves index and range‑query performance, and provides built‑in conversion functions, while also noting readability drawbacks and offering Java utilities for manual conversion.
This article explains how MySQL implicitly converts mismatched column types—such as comparing a VARCHAR column with a numeric literal—into floating‑point numbers, causing seemingly equal values to match, losing index usage, and potentially producing incorrect query results.
The article explains how MySQL's Online DDL handles VARCHAR column expansions, demonstrates performance differences across character sets using sysbench, and clarifies why certain size changes trigger in‑place alterations while others require full table copies, providing practical guidance for DBAs.
This article explains MySQL's row size restrictions, the differences between server and InnoDB limits, how VARCHAR and TEXT columns affect those limits, and demonstrates through calculations and tests the maximum count of TEXT fields that can be stored under various strict‑mode settings.
The article explains how omitting single quotes around a VARCHAR value in MySQL queries causes implicit type conversion, leading to full table scans and index loss, illustrated with MyBatis examples and detailed code snippets.
This article demonstrates how to store 5 MB images in MySQL using LONGBLOB, LONGTEXT, and VARCHAR columns, compares their disk usage, shows insertion scripts, and provides retrieval methods including a stored procedure for binary export, concluding that storing file paths is more efficient.
Replacing a VARCHAR column that stores timestamp strings with a native TIMESTAMP type in a massive sorting table reduced query time from over 20 minutes to 39 seconds, demonstrating a 30‑plus‑fold speedup due to smaller storage, faster CPU comparisons, and reduced I/O.