In this blog post, we’ll look at ways you can use MySQL 5.7 generated columns (or virtual columns) to improve query performance.
Introduction
About two years ago I published a blog post about Generated (Virtual) Columns in MySQL 5.7. Since then, it’s been one of my favorite features in the MySQL 5.7 release. The reason is simple: […]
In this blog post, I’ll provide an explanation why you should avoid using the CREATE TABLE AS SELECT statement.
The SQL statement “create table <table_name> as select …” is used to create a normal or temporary table and materialize the result of the select. Some applications use this construct to create a copy of the table. […]
In this blog post, I investigate a case of spiking InnoDB Rows inserted in the absence of a write query, and find internal temporary tables to be the culprit.
Recently I was investigating an interesting case for a customer. We could see the regular spikes on a graph depicting “InnoDB rows inserted” metric (jumping from […]
In my previous blog post, I talked about new general tablespaces in MySQL 8.0. Recently MySQL 8.0.3-rc was released, which includes a new data dictionary. My goal is to create one million tables in MySQL and test the performance.
Background questions
Q: Why million tables in MySQL? Is it even realistic? How does this happen?
Usually, millions of tables […]
In this blog post, I’ll review how a hung MySQL transaction can cause the InnoDB history length to grow and negatively affect MySQL performance.
Recently I was helping a customer discover why SELECT queries were running slower and slower until the server restarts (which got things back to normal). It took some time to get […]
In this blog, we’ll look at how queries in systems with parallel processing can return rows in a non-deterministic order (and how to fix it).
Short story:
Do not rely on the order of your rows if your query does not use
ORDER BY. Even with
ORDER BY, rows with the same values can be sorted differently. To […]
This blog shares some column store database benchmark results, and compares the query performance of MariaDB ColumnStore v. 1.0.7 (based on InfiniDB), Clickhouse and Apache Spark.
I’ve already written about ClickHouse (Column Store database).
The purpose of the benchmark is to see how these three solutions work on a single big server, with many CPU cores and large amounts of RAM. […]
For this blog post, I’ve decided to try ClickHouse: an open source column-oriented database management system developed by Yandex (it currently powers Yandex.Metrica, the world’s second-largest web analytics platform).
In my previous set of posts, I tested Apache Spark for big data analysis and used Wikipedia page statistics as a data source. I’ve used the same data as […]
In this blog, we’ll discuss descending indexes in MySQL 8.0.
Summary
The future MySQL 8.0 will (probably) have a great new feature: support for index sort order on disk (i.e., indexes can be physically sorted in descending order). In the MySQL 8.0 Labs release (new optimizer preview), when you create an index you can specify the order […]
This blog post discusses the ramifications of STRICT mode in MySQL 5.7.
In short
By default, MySQL 5.7 is much “stricter” than older versions of MySQL. That can make your application fail. To temporarily fix this, change the
SQL_MODE to
NO_ENGINE_SUBSTITUTION (same as in MySQL 5.6):
MySQL
mysql> set global SQL_MODE=”NO_ENGINE_SUBSTITUTION”;
1
mysql> set global SQL_MODE=”NO_ENGINE_SUBSTITUTION”;
MySQL 5.7, dates and default values
The default
SQL_MODE in […]
