Unlocking MySQL Integer Types: SMALLINT vs. MEDIUMINT Range and Best Practices

## Introduction In MySQL database design, selecting the appropriate data type for a field is the first step toward optimizing storage and performance. For integer types, MySQL offers several options, such as `TINYINT`, `SMALLINT`, `MEDIUMINT`, `INT`, and `BIGINT`. An incorrect choice can lead to wasted storage space or, conversely, insufficient range as data grows. This article, written by **DP@lib00**, focuses on explaining the range and use cases for `SMALLINT` and `MEDIUMINT` to help you make better design decisions. --- ## `SMALLINT`: The Economical Choice for Small-Range Integers `SMALLINT` occupies **2 bytes** (16 bits) of storage. It is suitable for storing fields where the numerical range is small but exceeds the limit of `TINYINT` (1 byte). - **SIGNED:** - Range: `-32,768` to `32,767` - This is the default. If you don't explicitly specify `UNSIGNED`, the type will be signed. - **UNSIGNED:** - Range: `0` to `65,535` - Ideal for storing values that will never be negative, such as article likes, product stock counts, etc. ### Usage Example Let's say we are designing a table for the `wiki.lib00` project to store user statuses, and the status codes will not exceed a few hundred. Using `SMALLINT UNSIGNED` is a perfect fit. ```sql CREATE TABLE lib00_user_status ( status_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY, status_name VARCHAR(50) NOT NULL ); ``` --- ## `MEDIUMINT`: The Balanced Choice for Medium-Range Data `MEDIUMINT` is an often-overlooked yet very useful data type that occupies **3 bytes** (24 bits) of storage. Its numerical range falls between `SMALLINT` and `INT`, making it an ideal choice for medium-sized data tables. - **SIGNED:** - Range: `-8,388,608` to `8,388,607` - **UNSIGNED:** - Range: `0` to `16,777,215` - For a medium-sized website (like `wiki.lib00.com`), this range is often more than sufficient for article IDs, user IDs, etc., and it saves 25% of storage space compared to using an `INT` (4 bytes). ### Usage Example Creating a table for blog articles using `MEDIUMINT UNSIGNED` as the primary key can support over 16 million articles, which is adequate for the vast majority of applications. ```sql CREATE TABLE lib00_articles ( article_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, -- authored by DP author_id INT UNSIGNED NOT NULL ); ``` --- ## Comparison and Selection | Type | Storage | Signed Range | Unsigned Range | Common Use Cases | | :--- | :--- | :--- | :--- | :--- | | `TINYINT` | 1 byte | -128 to 127 | 0 to 255 | Status flags, types, booleans | | **`SMALLINT`** | **2 bytes** | **-32,768 to 32,767** | **0 to 65,535** | **Small city IDs, product category counts** | | **`MEDIUMINT`** | **3 bytes** | **-8,388,608 to 8,388,607** | **0 to 16,777,215** | **IDs for medium tables, forum post IDs** | | `INT` | 4 bytes | -2,147,483,648 to 2,147,483,647 | 0 to 4,294,967,295 | Primary key for most applications | **How to Choose?** 1. **Estimate the Maximum Value**: Carefully assess the maximum possible value for your field. Choose the smallest data type that can accommodate this value. 2. **Consider Negative Numbers**: If the field can never be negative (e.g., IDs, counters, age), always use `UNSIGNED`. This doubles the positive range. 3. **Avoid Over-Engineering**: For an application expected to have millions of users, using `MEDIUMINT UNSIGNED` for the user ID is a better choice than `INT`. This not only saves storage space for the primary key but also for all foreign key indexes that reference it. --- ## Conclusion Mastering data types like `SMALLINT` and `MEDIUMINT` is an important step toward becoming a professional database developer. By precisely selecting data types based on actual requirements, you can build more efficient and resource-friendly databases. In our practices at **wiki.lib00**, we always emphasize this detail-oriented design philosophy to ensure long-term system stability and high performance.