Fueling innovative software
July 15-18, 2019
Portland, OR

How a modern database gets your data fast: MariaDB query optimizer

Vicențiu Ciorbaru (MariaDB Foundation)
4:15pm–4:55pm Thursday, July 18, 2019
Building Data-Intensive Applications
 Location: E143/144
Secondary topics:  Data Driven
Average rating: *****
(5.00, 1 rating)

Who is this presentation for?

  • Software developers, database administrators, and analysts

Level

Intermediate

Description

A modern database query optimizer needs to be able to handle a lot of different workloads, especially when it comes to general-purpose databases. Most of the time, it does a good job; however, certain patterns are hard to predict. With so many moving parts, it’s hard for the average DBA or database developer to come up with a good explanation for why the optimizer chooses certain query plans.

Vicențiu Ciorbaru dives deep into the optimizer, covering strategies for dynamic query rewriting, including derived-table merging and condition push down; temporary table reuse; join optimizations via IN/EXISTS subqueries; condition selectivity estimations via precollected histograms; and improved analytics via Window functions and CTEs. Armed with this extra information, you’ll be able to better tune problematic queries and better model a database schema, especially for large-scale datasets.

Vicențiu focuses on MariaDB’s optimizer point of view; however, he examines other database systems where it’s relevant.

Prerequisite knowledge

  • Familiarity with SQL language
  • Experience as a DBA (useful but not required)
  • A basic understanding of web and database application development (useful but not required)

What you'll learn

  • Learn how a database optimizer generates a query plan and how it optimizes it
  • Discover how to think about your data when planning a large operational or data warehouse database
  • Understand the weak points of a database optimizer and how to get around them
Photo of Vicențiu Ciorbaru

Vicențiu Ciorbaru

MariaDB Foundation

Vicențiu Ciorbaru is a server developer for the MariaDB Foundation and de facto team lead of junior developers and new contributors. His past projects include roles in MariaDB, Window functions, and custom aggregate functions. He also ensures MariaDB remains stable and up-to-date in Linux distributions by handling distribution-specific bugs, especially in Debian.

Comments on this page are now closed.

Comments

Picture of Vicențiu Ciorbaru
Vicențiu Ciorbaru | Software Development Team Leader
04/19/2019 6:34am PDT

The talk will focus on optimizations available in MariaDB, however there are quite a few slides that explain the differences between MySQL and MariaDB, as well as where both databases implement the same optimization technique.

Anthony Esposito | Solutions Architect
04/19/2019 6:25am PDT

Will this session be applicable to those who run MySQL from Oracle?