When designing a database for billions of phone numbers, using INT or BIGINT leads to overflow, data‑integrity loss, and inefficient queries, while VARCHAR(20) preserves formatting, supports international extensions, simplifies validation, and avoids common development pitfalls.
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.
This article explains the fundamental differences between MySQL VARCHAR and CHAR types, covering their definitions, length limits, storage overhead, character‑set effects, practical examples, and guidelines for choosing the appropriate column type in database design.
This article examines common misconceptions about MySQL VARCHAR—why 255 was historically used, the real maximum length, performance differences between short and long definitions, character‑set impacts on storage, and why aligning sizes to powers of two offers no real benefit.
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 length of VARCHAR columns in MySQL affects both the physical storage size of tables and the execution speed of various queries, revealing that storage remains unchanged while long VARCHARs can dramatically slow down full‑table sorts.
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.
This article investigates whether MySQL varchar column length influences storage size and query performance by creating two tables with varchar(50) and varchar(500), inserting one million rows, measuring disk usage, and benchmarking various queries, ultimately revealing that storage is identical while longer columns can degrade performance during sorted scans due to increased temporary file usage.
This article investigates whether defining a VARCHAR column with a larger length (e.g., VARCHAR(500) vs VARCHAR(50)) changes storage consumption and query speed in MySQL by creating two identical tables, inserting one million rows, measuring space usage, and benchmarking various SELECT and ORDER BY operations.
This article investigates whether MySQL VARCHAR(50) and VARCHAR(500) occupy identical storage and how column length impacts query performance, providing step‑by‑step table creation, bulk data insertion, storage queries, and detailed benchmark results with analysis of the underlying causes.
This article investigates whether defining a VARCHAR column with a larger length affects MySQL storage space and query performance by creating two tables with VARCHAR(50) and VARCHAR(500), inserting one million rows, and measuring storage usage and execution times for various queries.
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.
This article explores the true maximum length of MySQL VARCHAR columns, showing how charset, NULLability, column count, and row format affect the limit, and explains the underlying storage mechanics, row overflow, and alternatives for data larger than 64 KB.
This article debunks the common belief that MySQL VARCHAR can store up to 65535 characters, showing how the true limit depends on the chosen character set, NULLability, column count, and row‑format details, and explains how oversized values are stored or require TEXT/BLOB types.
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 explores how MySQL determines the maximum length of VARCHAR columns, showing that the limit depends on the chosen character set, nullability, and column count, and explains the underlying row storage format and how to handle oversized strings.
This article investigates the true maximum length of MySQL VARCHAR columns, showing how charset byte size, NULLability, column count, and the InnoDB Dynamic row format together determine the effective limit, and explains how overflow is handled for very large strings.
This article explains where MySQL keeps its data files, how InnoDB organizes tablespaces into segments, extents, pages and rows, details the Compact row format—including variable‑length field length lists, NULL‑value lists, and hidden fields—and clarifies the limits of VARCHAR and row‑overflow handling.
This article explains InnoDB’s role as a MySQL storage engine, how it reads and writes data using 16 KB pages, the mechanics of row formats—especially the dynamic format—and why varchar columns are limited to 16383 characters, illustrated with SQL examples and diagrams.
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 InnoDB's storage engine mechanics, page-based I/O, the default Dynamic row format, how variable‑length fields like VARCHAR are recorded, why the maximum usable length is 16383 characters in utf8mb4, and the handling of NULL lists and overflow columns.
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 experimentally determines the maximum number of characters a MySQL 5.7 VARCHAR column can hold under different character sets, explains the underlying row‑size and index‑key restrictions, and provides concrete CREATE TABLE examples for latin1, utf8, and utf8mb4.
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.
This article explains how VARCHAR's variable‑length storage and CHAR's fixed‑length storage differ in space usage, memory consumption during queries, and I/O performance, helping developers select the appropriate type for optimal MySQL efficiency.
CHAR stores fixed‑length strings padded with spaces, while VARCHAR stores variable‑length strings with a length byte, leading to different maximum sizes—255 characters for CHAR and up to 65,535 bytes for VARCHAR (about 21,589 UTF‑8 characters)—and distinct use cases such as short fixed fields versus space‑saving flexible fields.
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.
