TechPrufer
Get started
PlanetScale
PlanetScale logoPlanetScale · Feature trail

MySQL Database Design Best Practices

7 posts · Mar 2023 – Feb 2024

What this trail covers

PlanetScale offers robust support for MySQL and MariaDB, including advanced features for handling geospatial data and scaling options. This post details how to represent and query geographic features using MySQL's spatial data types and functions, enabling engineers to work with location-based information effectively within their databases. It covers data types like POINT, LINESTRING, and POLYGON, spatial formats (WKT, WKB), and common spatial functions for distance calculations, intersection ch. This post also details the process of upgrading from MySQL 5.7 to 8.0, covering character set changes, obsolete data types, authentication updates, new reserved words, SQL mode adjustments, C-style operator deprecation, server error code changes, and a zero-downtime upgrade path using PlanetScale's import tool. It highlights the benefits of upgrading to MySQL 8.0, including performance improvements and enhanced security features, and emphasizes the importance of performing this upgrade before the end-of-life for MySQL 5.7.

All PlanetScale trails
  1. Three common MySQL database design mistakesThis post details three common MySQL database design mistakes: suboptimal data type selection (e.g., INT for IDs running out of space, or over-provisioning for zip codes), the impact of missing or redundant indexes on query performance and storage costs, and the improper storage of semi-structured data by using TEXT columns instead of the native JSON type. It offers solutions like using appropriate integer sizes, managing indexes effectively, and leveraging MySQL's JSON capabilities for efficient querying and indexing.
  2. Working with Geospatial Features in MySQLThis post introduces the representation and querying of geospatial features within MySQL. It details MySQL's spatial data types (POINT, LINESTRING, POLYGON, etc.), supported spatial data formats (WKT, WKB, internal with SRID), and provides examples of creating tables with spatial columns and using spatial functions like ST_GeomFromText, ST_X, ST_Y, ST_Distance, ST_Contains, ST_Intersects, ST_Buffer, ST_Union, and ST_Difference. It also highlights MySQL 8's improved support for spatial reference systems (SRID) and spatial indexing.
  3. Datetimes versus timestamps in MySQLThis post details the differences between MySQL's DATE, DATETIME, TIMESTAMP, YEAR, and TIME column types. It explains their storage requirements, legal ranges, and specific use cases. A key focus is the distinction between DATETIME and TIMESTAMP, particularly the '2038 problem' for TIMESTAMP and how TIMESTAMP handles timezones by converting to/from UTC, while DATETIME does not.
  4. Character sets and collations in MySQLThis post provides a deep dive into MySQL character sets and collations, explaining their definitions, how to query them, and the critical differences between `utf8` and `utf8mb4`. It details how to define character sets and collations at various levels (column, table, server) and illustrates the impact of different collation suffixes (`_ai`, `_ci`, `_bin`, etc.) on string comparisons. The post also touches upon MySQL's spatial data types and functions for handling geographic data, integrating this with PlanetScale's database management features.
  5. MariaDB vs. MySQLThis post compares MariaDB and MySQL, highlighting their differences, scaling options, and the role of Vitess. It details how to use spatial data types and functions in MySQL for location-based data, including data types, spatial formats, and common spatial functions. The post also touches upon Vitess's role in scaling these databases.
  7. Why isn’t MySQL using my index?This post provides a deep dive into common reasons why MySQL might not use an index, even when one is defined. It explains how to use `EXPLAIN` to diagnose index usage, discusses index selectivity and cardinality, and highlights the impact of outdated statistics. It also covers index limitations, such as wildcard searches and applying functions to indexed columns, and explains scenarios where a table scan might be faster than using an index. The post uses a concrete `people` table example to illustrate these concepts.
  8. How to Upgrade from MySQL 5.7 to 8.0This post details the process of upgrading from MySQL 5.7 to 8.0, covering character set changes (utf8mb4, utf8mb4_0900_ai_ci), obsolete data types (YEAR(2), ENUM, TINYTEXT/MEDIUMTEXT/LONGTEXT), authentication changes (caching_sha2_password), new reserved words, SQL mode changes (ONLY_FULL_GROUP_BY, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION), C-style operator deprecation (&&, ||, !), and server error code changes. It also introduces a zero-downtime upgrade path using PlanetScale's import tool, which acts as a replica during the migration and automatically handles the upgrade to 8.0. The post emphasizes the importance of performing this upgrade before the end-of-life for MySQL 5.7.