Here is the third in a series of posts about CTEs, a new feature of MySQL 8.0, available in this Labs release. In the first post we had explored the new SQL syntax, and in the second we had applied it to generating series.…
Starting with the 8.0 optimizer labs release the MySQL server now supports descending indexes. As I will detail in this post, this new feature can be used to eliminate the need for sorting results, and lead to performance improvements in a number of queries.…
Today was the first day of sessions at Percona Live Europe 2016, and it was packed with exciting talks and important information on open source databases. Some of the most anticipated talks are those that cover what to expect in MySQL 8.0.
One of those talks was given by Oracle’s Manyi Lu, Director of Software Development. She discussed MySQL 8.0: what’s new in Optimizer.
In her talk, Manyi discussed what we could look forward to in MySQL 8.0’s optimizer. There are substantial improvements in the optimizer in MySQL 5.7 and MySQL 8.0. Most noticeably, users can now combine relational data with NoSQL using the new JSON features. MySQL also now supports functional indexes through generated columns. …
[Read more]One of the things we have been working on for MySQL 8, is speeding up scans that read multiple records from a table or an index. Such scans benefit from reading the records in batches, so that they don’t get the overhead of latching data pages and navigating in the B-tree for every single record that is read.…
In this blog, I will provide answers to the Q & A for the “Introduction into storage engine troubleshooting” webinar.
First, I want to thank everybody for attending the July 14 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:
Q: At which isolation level do
pt-online-schema-change
and
pt-archive
copy data from a table?
A: Both tools do not change the server’s default transaction isolation level. Use either
REPEATABLE READ
or …
[Read more]
The optimizer in MySQL 5.7 leverages generated columns. Generated
columns will physically store data in two cases: Either the
column is defined as STORED or you create an index on a virtual
column. The optimizer will leverage such an index automatically
if it encounters the same expression in a statement. Let's see an
example:
mysql> DESC squares;
+-------+------------------+------+-----+---------+-------+
| Field |
Type
| Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| dx | int(10) unsigned | YES
| | NULL
| |
| dy | int(10) unsigned | YES
| | NULL
| |
…
In a previous post I took the new JSON datatype and functions for a test drive, using sample data provided by SF OpenData. Today I will extend that example to include indexing.
Introducing Generated Columns
MySQL 5.7 introduces a new feature called generated columns.…
In a recent blog post my colleague Roy presented his work for MySQL 5.7 that makes the query optimizer merge sub-queries in the FROM clause (so-called derived tables) with the outer query. I will in this blog post show an example of how this work improves the performance of a MySQL query.…
In MySQL 5.7, we have enhanced the optimizer so that derived tables and views are handled more consistently.
Just to recap: A derived table is a subquery that can take the place of a table in the FROM clause of an SQL statement.…