When working with MySQL (or any database!), it's essential to understand how indexes work and how they can be used to improve the efficiency of queries. An index is a separate data structure that maintains a copy of part of your data, structured to allow quick data retrieval. Usually, this structure is a B+ Tree. We have an entire post on how indexes work if you want to go into greater detail. Obfuscated indexes Creating indexes is only part of the battle. You must also know how to write your queries so that you allow MySQL to use your indexes. One common mistake people make when writing queries is that they obfuscate their indexes. Obfuscating an index simply means that you're hiding the indexed value from MySQL. Let's say you have a todos table with a created_at column that records a timestamp of when the record was created.CREATE TABLE `todos` ( `id` int NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `created_at` timestamp NOT NULL …

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 has one default collation.| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN | …

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. Determining what index is being used Before you can determine why your index isn't being used, you must first determine that your index isn't being used. You can run an EXPLAIN on …

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 is inherently better than the other. They each have their own strengths and weaknesses. The importance of deterministic ordering Before we talk about the wonders of pagination, we need to …

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: The ID of the query — The column always contains a number, which identifies the SELECT to which the row belongs. The SELECT_TYPE — If you are running a SELECT, MySQL divides SELECT queries into simple and primary …

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? 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), but it is gRPC compatible. This HTTP interface led to the development of our Serverless driver for JavaScript and PlanetScale Connect. In serverless compute …

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 CREATE TABLE statement:CREATE TABLE my_table ( my_integer_col INT );

Since MySQL defaults to allowing both negative and positive numbers, my_integer_col would be able to store whole …