MySQL, MariaDB and Friends
Knocking down the barriers of ORDER BY LIMIT queries with MariaDB 10.5
The talk will start with a recap of how MariaDB(or MySQL) handles the
ORDER BY LIMIT optimization and examples demonstrating why the current
optimizer is not good enough.
Further, the talk will describe how the optimizer in MariaDB 10.5 mostly
solves the issue, the remaining unresolved issues and how DBAs can tackle them.
FULL DESCRIPTION:
For the first part of the talk, I will discuss the possible strategies by
which ORDER BY LIMIT optimization is handled in MariaDB (or MySQL)
The strategies are:
1) Using an ordered index (ref, range or index scan)
2) Using filesort on the first non-const table
3) Using filesort on the temporary table, that stores the output of the join
Then I will discuss how the current MariaDB/MySQL optimizer makes the choice between the strategies and show
the situations where it will never get a good query plan
For the second part of the talk, I will describe how a new cost-based
optimization in MariaDB 10.5 solves the above issue.
The talk will contain details about how the costs were taken into account
during the optimization phase. Further, with the help of examples
I would demonstrate how the execution differs for this new optimization
and how this leads to improved performance for ORDER BY LIMIT queries.
Additional information
| Type | devroom |
|---|
More sessions
| 2/1/20 |
Welcome to the FOSDEM MySQL, MariaDB & Friends Devroom 2020
|
| 2/1/20 |
MySQL 8 and MariaDB 10.4 are the latest Major versions for MySQL and MariaDB. While MariaDB started by being slightly different MySQL variant, now it has grown into very much different database platforms which grows more different from every release. In this presentation, we will look into the differences between MySQL and MariaDB in the core areas such as SQL features, query optimizations, replication, storage engines, and security as well as discuss unique features and capabilities MySQL 8 and ...
|
| 2/1/20 |
In this talk, we'll walk through RocksDB technology and look into areas where MyRocks is a good fit by comparison to other engines such as InnoDB. We will go over internals, benchmarks, and tuning of MyRocks engine. We also aim to explore the benefits of using MyRocks within the MySQL ecosystem. Attendees will be able to conclude with the latest development of tools and integration within MySQL.
|
| 2/1/20 |
It is common knowledge that built-in asynchronous master-master (active-active) replication is not safe. I remember times when the official MySQL User Reference Manual stated that such an installation is not recommended for production use. Some experts repeat this claim even now. While this statement is generally true, I worked with thousands of shops that successfully avoided asynchronous replication limitations in active-active setups. In this talk, I will show how they did it, demonstrate ...
|
| 2/1/20 |
Have you ever needed to get some additional write throughput from MySQL ? If yes, you probably found that setting sync_binlog to 0 (and trx_commit to 2) gives you an extra performance boost. As all such easy optimisation, it comes at a cost. This talk explains how this tuning works, presents its consequences and makes recommendations to avoid them. This will bring us to the details of how MySQL commits transactions and how those are replicated to slaves. Come to this talk to learn how to get the ...
|
| 2/1/20 |
MariaDB/MySQL/Percona Server provide some features in this space, but currently there isn't one product that covers all the needs (at least not available as FOSS). This talk will provide an overview of Data-at-Rest-Encryption features in MySQL, MariaDB and Percona Server for MySQL, their availability across versions, and status (experimental/GA).
|
| 2/1/20 |
ProxySQL, the high performance, high availability, protocol-aware proxy for MySQL is now GA in version 2.0. This version introduces several new features, like causal reads using GTID, better support for AWS Aurora, native support for Galera Cluster, LDAP authentication and SSL for client connections. This session provides an overview of the most important new features.
|
