If you are looking to improve the performance of MySQL databases in your software, you may need to know all the differences between InnoDB and MyISAM, two well-known types of MySQL storage engines. And if you are about to choose one of them, we believe you would like to find out what each of them […]

The post InnoDB vs MyISAM: A Detailed Comparison of Two MySQL Storage Engines appeared first on Devart Blog.

This article focuses on the database storage engines for MySQL that ensure appropriate performance and manage SQL operations for multiple table types. The article examines the differences between the most widely used MySQL storage engines. MySQL is the second most popular Relational Database Management Systems (RDBMS) in the world. Countless services and applications have MySQL […]

The post Storage Engines in MySQL appeared first on Devart Blog.

What's a good natural key? This is a very difficult question for most entities when you design your schema. In some rare cases, there seems to be an "obvious" candidate, such as a variety of ISO standards, including: ISO 639 language codes ISO 3166 country codes ISO 4217 currency codes But even in those cases, … Continue reading The Cost of Useless Surrogate Keys in Relationship Tables →

There are a number of options for generating ID values for your tables. In this post, Alexey Mikotkin of Devart explores your choices for generating identifiers with a look at auto_increment, triggers, UUID and sequences.

AUTO_INCREMENT

Frequently, we happen to need to fill tables with unique identifiers. Naturally, the first example of such identifiers is PRIMARY KEY data. These are usually integer values hidden from the user since their specific values are unimportant.

When adding a row to a table, you need to take this new key value from somewhere. You can set up your own process of generating a new identifier, but MySQL comes to the aid of the user with the AUTO_INCREMENT column setting. It is set as a column attribute and allows you to generate unique integer identifiers. As an example, consider the …

Due to continuous development and improvement, Percona Server for MySQL incorporates a number of improvements related to binary log handling and replication. This results in replication specifics, distinguishing it from MySQL Server.

Temporary tables and mixed logging format Summary of the fix:

As soon as some statement involving temporary tables was met when using a mixed binlog format, MySQL switched to row-based logging for all statements until the end of the session (or until all temporary tables used in the session were dropped). This is inconvenient when you have long-lasting connections, including replication-related ones. Percona Server for MySQL fixes the situation by switching between …

In this blog post, I’ll discuss some of the MySQL and MariaDB default configuration differences, focusing on MySQL 5.7 and MariaDB 10.2. Percona Server for MySQL uses the same defaults as MySQL, so I will not list them separately.

MariaDB Server is a general purpose open source database, created by the founders of MySQL. MariaDB Server (referred to as MariaDB for brevity) has similar roots as Percona Server for MySQL, but is quickly diverging from MySQL compatibility and growing on its own. MariaDB has become the default installation for several operating systems (such as Red Hat Enterprise Linux/CentOS/Fedora). Changes in the default variables can make a large difference in the out-of-box …

TL;DR: we still need MyISAM and myisampack because it uses less space on disk (half of compressed InnoDB) !

In the previous post, I shared my experience with InnoDB table compression on a read-only dataset.  In it, I claimed, without giving much detail, that using MyISAM and myisampack would result is a more compact storage on disk.  In this post, I will share more details about this claim.

In my last post about big MySQL deployments, I am quickly mentioning that InnoDB compression is allowing dividing disk usage by about 4.3 on a 200+ TiB dataset.  In this post, I will give more information about this specific use case of InnoDB table compression and I will share some statistics and learnings on this system and subject.  Note that I am not covering InnoDB page compression which is

MySQL theoretical limits are known and they can be found in the manual, they include:

MyISAM permits data and index files to grow up to 256 TiB by default, but this limit can be changed up to the maximum permissible size of 65,536 TiB (256^7 − 1 bytes). The maximum tablespace size depends on the InnoDB page size: 64 TiB for 16 KiB pages.(The maximum tablespace size is also the maximum size for a

This blog discusses the gradual end of MyISAM in MySQL.

The story that started 20 years ago is coming to its end. I’m talking about the old MyISAM storage engine that was the only storage provided by MySQL in 1995, and was available in MySQL for 20+ years. Actually, part of my job as a MySQL consultant for 10+ years was to discover MyISAM tables and advise customers how to convert those to InnoDB.

(Check your MySQL installation, you may still have MyISAM tables).

MySQL 5.7 still used MyISAM storage for the system tables in the MySQL schema.

In MySQL 8.0 (DMR version as of writing), the MyISAM storage engine is still available. But in a very limited scope:

• After …