MySQL Integer Data Types

5 posts · Sep 2022 – Mar 2024

What this trail covers

PlanetScale offers robust support for MySQL and MariaDB, including advanced features for handling JSON data types and optimizing the use of primary keys. This post details the JSON data type in MySQL, its benefits, caveats, and common operations. It explains when to use JSON, its storage footprint, performance implications, and provides examples of using JSON functions. Additionally, it addresses the performance implications of using UUIDs as primary keys in MySQL, recommending binary storage, and introduces generated hash columns for efficient indexing of large or composite values.

All PlanetScale trails

Mar 2024The Problem with Using a UUID Primary Key in MySQLThis post details the performance implications of using UUIDs as primary keys in MySQL, contrasting them with auto-incrementing integers. It explains how random UUIDs can lead to B+ Tree page splitting and fragmentation, negatively impacting insert performance and storage utilization. The post recommends using the `BINARY(16)` data type for UUIDs, opting for ordered UUID variants (v6, v7), and utilizing MySQL's `UUID_TO_BIN` function with the 'swap flag'. It also suggests alternative ID types like Snowflake IDs, ULIDs, and NanoIDs, and references PlanetScale's use of NanoIDs for its API.
Jun 2023Generated Hash Columns in MySQLThis post introduces and details the use of generated hash columns in MySQL for optimizing indexed lookups. It explains how generated columns (STORED and VIRTUAL) work, demonstrates their use for calculating derived values like radius and area, and then focuses on creating generated hash columns using MD5. It covers scenarios for single large columns (like URLs) and multiple columns using CONCAT_WS, and discusses optimizing storage by using BINARY(16) for MD5 hashes with UNHEX. The post also addresses hash collision mitigation by using redundant conditions and highlights the benefits of compact indexes over wide composite indexes for specific lookup patterns.
Oct 2022MySQL Integers: INT BIGINT and moreThis post provides a detailed explanation of MySQL's integer data types (INT, BIGINT, MEDIUMINT, SMALLINT, TINYINT), their signed and unsigned ranges, and how they are stored using binary representation. It clarifies the use of the `ZEROFILL` attribute and offers guidance on selecting the appropriate integer type based on storage needs and whether negative numbers are required.
Sep 2022MySQL data types: VARCHAR and CHARThis post clarifies the differences between VARCHAR and CHAR data types in MySQL. It explains VARCHAR's variable length storage with overhead bytes and CHAR's fixed length storage with right-padding. It also details how character sets like utf8mb4 impact storage allocation and provides guidance on choosing between VARCHAR and CHAR based on string length and the need to preserve trailing spaces.
Sep 2022The MySQL JSON data typeThis post introduces and explains the MySQL JSON data type, covering its structure, use cases (log output, API responses, configuration data, denormalization), and caveats (storage overhead, indexing limitations). It demonstrates common operations using MySQL JSON functions such as JSON_ARRAY, JSON_TYPE, JSON_EXTRACT, and JSON_ARRAY_APPEND, and provides examples of table creation and data insertion. It also references related articles on MySQL integer and VARCHAR data types.
Advertisement