1 views collected around this technical thread.
This article explains the materialized view pattern, describing why pre‑populated views are created when source data formats hinder efficient queries, how they improve performance as disposable caches, and the key considerations, trade‑offs, and scenarios for adopting this approach.
An interview-style SQL challenge asks you to identify records where a professor teaches a course belonging to a different college than their own, providing table definitions, sample data, and the expected result, while inviting readers to devise the correct query.
This guide explains how to design a database schema with optional tree_path, define a generic ITreeNode interface, implement a versatile TreeNodeUtil class in SpringBoot, and demonstrates comprehensive tests—including building, filtering, and path generation—for reusable multi‑level structures such as menus, comments, departments, and categories.
This article explains MySQL's index limits for InnoDB and MyISAM engines, detailing the maximum number of indexes per table, the maximum columns per index, and provides practical recommendations on how many indexes a table should have to balance performance and maintenance.
The article examines the challenges of generating short, user‑friendly numeric account IDs, evaluates Snowflake and MySQL auto‑increment approaches, discusses deadlock issues with REPLACE INTO, and presents a final segment‑based solution that allocates ID blocks per login server while avoiding waste and concurrency problems.
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 examines the difficulties of adding new fields to a core billion‑row MySQL table—including locking, page splitting, and index degradation—and presents a configuration‑driven, three‑layer architecture that uses JSON extension fields, extension tables, and Elasticsearch to achieve safe, scalable dynamic schema evolution.
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.
This article explains why database normalization is essential, defines the first three normal forms with examples, discusses their advantages and disadvantages, and illustrates table relationships and practical design considerations to help you create efficient, low‑redundancy relational databases.
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 discusses the problem of MySQL auto‑increment ID exhaustion and presents six practical solutions—including changing column type to BIGINT, using UUIDs, segmenting ID generation, composite keys, adjusting auto‑increment steps, and database sharding—to ensure scalability and uniqueness.
The article explains why using UUIDs as primary keys in MySQL large tables leads to poor index efficiency, slower inserts and queries, and costly index refreshes on updates, and then presents practical optimization techniques such as ordered UUIDs, binary storage, hybrid auto‑increment keys, and table partitioning.
This article explains InnoDB clustered and secondary indexes, their physical B‑tree storage, page‑size and fill‑factor settings, the three‑phase sorted index build process, and the design, tables, cache, document‑ID handling, transaction semantics, and monitoring of InnoDB full‑text indexes.
This article investigates MySQL’s recommendation against UUIDs, comparing auto‑increment, UUID, and random snowflake keys through insertion speed tests and index structure analysis, revealing why auto‑increment keys outperform others, while also discussing the drawbacks of each approach.
This article investigates MySQL's recommendation against using UUIDs or non‑sequential keys as primary keys by creating three tables, benchmarking insert and query speeds with Spring Boot/JdbcTemplate, analyzing index structures, and discussing the trade‑offs of auto‑increment, UUID, and random long keys.
The article explains how to design a MySQL table for product sales statistics and compares several query strategies—including loop queries, OR concatenation, mixed filtering, and finally SQL‑92 row‑by‑row comparison—to efficiently retrieve sales data for multiple business units and their dynamic product lists while respecting development constraints.
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 explains the concept, advantages, and technical choices of multi‑tenant architecture, then details a design using Spring Boot and Spring Cloud, covering database strategies, deployment isolation, tenant management, code examples, and step‑by‑step implementation for SaaS applications.
This article explains MySQL's various numeric, date/time, and string data types, discusses their characteristics such as signed/unsigned integers, floating‑point precision, fixed‑point accuracy, and provides practical best‑practice recommendations for choosing optimal types in database design.
Activiti is a BPMN workflow engine that provides components such as Process, Repository, Runtime, Task, and History services, stores definitions, runtime data, and history in a relational database schema, and is used by deploying BPMN diagrams, starting instances, and managing tasks via its Java API.