Character sets and collations are fundamentally important concepts to understand when dealing with string columns in MySQL. A slight misunderstanding of either can lead to poor performance or unexpected errors when inserting data.

A character set defines the characters allowed to go in a column. A collation is a set of rules for comparing those characters. Each character set can have multiple collations, but a collation may only belong to one character set.

Character sets in MySQL

MySQL supports a wide range of character sets, which you can view by selecting from the information_schema database.

SELECT * FROM information_schema.character_sets ORDER BY character_set_name

This will list out all of the character sets, along with their default collations. Every character set …

One of the most frustrating experiences when dealing with databases is when you've designed the perfect index, but MySQL still doesn't use it. There are several reasons why this could be the case, and in this article, we'll explore some of the most common ones.

Throughout this article, we'll be working with a very simple people table that looks like this:

CREATE TABLE `people` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `state` char(2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `first_name` (`first_name`),
  KEY `state` (`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

We'll be adding and dropping keys throughout to show different scenarios, but this is a good starting place.

…

Everything you need to know about MySQL as an application developer, with a focus on improving query performance. After covering the high-level overview, we’ll put the learnings to the test with some hands-on examples.

Any good DBA will tell you to "select only what you need." It's one of the most common aphorisms, and for good reason! We don't ever want to select data that we're just going to throw away. One way this advice manifests itself is to not use SELECT * if you don't need all the columns. By limiting the columns returned, you're selecting only what you need.

Pagination is another way to "select only what you need." Although, this time, we're limiting the rows instead of the columns. Instead of pulling all the records out of the database, we only pull a single page that we're going to show to the user.

There are two primary ways to paginate in MySQL: offset/limit and cursors. Which method you choose depends on your use case and your application's requirements. Neither …

In the MySQL world, EXPLAIN is a keyword used to gain information about query execution. This blog post will demonstrate how to utilize MySQL EXPLAIN to remedy problematic queries.

On the Technical Solutions team here at PlanetScale, we frequently talk with users who seek advice regarding query performance. Although creating an EXPLAIN plan is relatively simple, the output isn’t exactly intuitive. It’s essential to understand its features and how to leverage it best to achieve performance goals.

EXPLAIN vs. EXPLAIN ANALYZE

When you prepend the EXPLAIN keyword to the beginning of a query, it explains how the database executes that query and the estimated costs. By leveraging this internal MySQL tool, you can observe the following: …

What does the MySQL 5.7 EOL means for your database? Learn considerations to upgrade 8.0 and how PlanetScale can help you upgrade with no downtime or data loss.

Learn about sharding, connection pooling, and more from PlanetScale Technical Solutions Architect Jonah Berquist.

Over here at PlanetScale, we offer you a MySQL database. As a part of this offering, it is critical that we offer you a MySQL protocol-compatible interface to access. This enables using mysql-client as well as any MySQL-compatible driver for your favorite language.

But what if we weren’t constrained by this? Could we provide an alternative interface and API?

Warning

Most of what I will be discussing is not publicly documented and is entirely experimental.The background

As a part of some of our infrastructure initiatives, we demanded new APIs and connectivity features for our database. To support features that weren’t available over the MySQL protocol, we decided to start bolting on a publicly accessible HTTP API. This API is not documented for public consumption just yet (it will be, I promise), …

MySQL has a number of integer types, and while INT may seem like the right choice for most scenarios, it’s worth understanding what options you have so you can make the right choice when designing your database. In this article, we’ll take a look at the various integer types and take a deeper dive into how they are stored in MySQL.

An overview of the MySQL INT type

An integer is simply a whole number. It can be positive, negative, or even zero. In MySQL, there are actually several different data types you can use to store integers, each with its own range of numbers. The standards INT type can store up to 4,294,967,296 values including 0, and MySQL permits negative numbers by default unless otherwise specified. Defining an INT column looks like this in a …

MySQL gave us the JSON data type back in mid-2015 with the release of MySQL 5.7.8. Since then, it has been used as a way to escape rigid column definitions and store JSON documents of all shapes and sizes: audit logs, configuration settings, 3rd party payloads, user-defined fields, and more.

Although MySQL gives us functions for reading and writing JSON data, you’ll quickly discover something that is conspicuously missing: the ability to directly index your JSON columns.

In other databases, the best way to directly index a JSON column is usually through a type of index known as a Generalized Inverted Index, or GIN for short. Since MySQL doesn’t offer GIN indexes, we’re unable to directly index an entire stored JSON document. All is not lost though, because MySQL does give us a way to indirectly index parts of our stored JSON …