« Local Functions and Allocations
‘in’ will make your code slower »

MySQL Best Practices for .NET

We advise the following best practices for using MySQL Server with .NET code.

General Recommendations

Increase max_allowed_packet

The max_allowed_packet server variable controls the maximum size of a SQL statement that can be sent to the server, or the largest individual row that can be returned. Before MySQL 8.0, this defaulted to 4MiB, which is too small for many applications.

In MySQL 8.0, the new default is 64MiB; if you’re running an earlier server version you should set max_allowed_packet=64M (or higher).

Use MySqlConnector

MySqlConnector is a replacement ADO.NET connector library (with many contributions from Faithlife developers). It provides true async I/O, has better performance, and fixes many bugs in MySQL Connector/NET (aka MySql.Data).

Data Schema

Use COLLATE utf8mb4_bin

All text columns should use the utf8mb4 character set, as it allows the full range of Unicode to be stored. (See In MySQL, never use “utf8”. Use “utf8mb4”. for more details.)

By default, we prefer to use a binary collation:

  • It’s faster
  • It matches C# semantics: "a" != "A"
  • Many stored string values are IDs or tokens, which don’t need case-insensitive comparison

If you have human-readable textual data where you’re certain that you want the database to perform non-ordinal equality comparison and ordering, then consider using utf8mb4_0900_ai_ci (or decide if another collation is more appropriate for your use case).

Note: The default character set has changed from latin1 to utf8mb4 in MySQL 8.0.

Store bool as TINYINT(1)

In MySQL Server, BOOL is an alias for TINYINT(1). The MySQL ADO.NET connector understands this convention and will marshal TINYINT(1) back to managed code as the C# bool type (System.Boolean).

Use the BOOL alias when defining columns in your SQL statements. Do not use BIT(1) (which gets mapped as a ulong) to represent a Boolean value.

Avoid TINYINT(1)

As a corollary to the above, avoid explicitly using TINYINT(1). If you need a one-byte integer, use TINYINT (or TINYINT UNSIGNED). The (1) suffix simply indicates the “display width” (which is typically ignored by .NET programs), not the number of bytes used for storage.

Store Guid as CHAR(36)

To store a Guid, use CHAR(36) [NOT NULL] COLLATE ascii_general_ci.

The MySQL UUID() function returns a GUID in this format. The MySQL ADO.NET connector understands this convention and will marshal CHAR(36) back to managed code as the .NET Guid type.

The collation is ascii_general_ci because:

  • Using a one-byte-per-character character set allows MySQL to use fixed-length storage
  • Index prefix length limits are measured in bytes, not characters
  • Case-insensitive collation allows GUIDs to be queried using uppercase or lowercase hex digits

If you’re storing millions of GUIDs, you may wish to consider using BINARY(16) and performing custom data transfer. This will save 20 bytes per value, which may significantly reduce your data storage needs. Note that for a BINARY(16) column, MySqlDataReader.GetValue will return a byte[], but MySqlDataReader.GetGuid will reinterpret the value as a Guid.

Connection String Options

CharacterSet=utf8mb4

Uses Unicode when transferring queries to and results from the server, avoiding potential data loss from encoding errors.

Note: this option is utf8mb4 by default in MySqlConnector

ConnectionReset=True

With connection pooling on, but ConnectionReset=False, temporary tables and session variables from the last connection to the database will be retained. This is almost never what you want, and can lead to bugs.

Note: This option is True by default in MySqlConnector

Posted by Bradley Grainger on October 30, 2017