Showing entries 21 to 30 of 107
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: optimizer (reset)
MySQL 8.0 Labs: [Recursive] Common Table Expressions in MySQL (CTEs), Part Three – hierarchies

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.…

MySQL 8.0 Labs – Descending Indexes in MySQL

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.…

Percona Live Europe 2016: “MySQL 8.0: what’s new in Optimizer” with Manyi Lu

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]
MySQL 8.0: Faster batch record retrieval

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.…

Introduction into storage engine troubleshooting: Q & A

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]
MySQL 5.7: Optimizer finds best index by expression

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    |       |

[Read more]
Indexing JSON documents via Virtual Columns

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.…

What to do with optimizer hints after an upgrade?

At a recent optimizer webinar, I talked about MySQL introducing a new style for hints, and that MySQL 5.7 also added support for more hints, see Sergey Glukhov’s blog. A question I got at the end of the webinar was what to do with the hints in the application code after an upgrade?…

MySQL 5.7: Improved Performance of Queries with Derived Tables

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.…

Derived Tables in MySQL 5.7

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.…

Showing entries 21 to 30 of 107
« 10 Newer Entries | 10 Older Entries »