Percona Live: Data Performance Conference 2016 Logo

April 18-21, 2016

Santa Clara, California

Exploring MariaDB Query Optimizer with ANALYZE for statements

Exploring MariaDB Query Optimizer with ANALYZE for statements

Experience level: 
Intermediate
Duration: 
50 minutes conference
Tracks:
Scalability/Performance
Topics:
MySQL
Optimization
Tools and Techniques

Description

MariaDB 10.1 is now stable, and one of its new features is ANALYZE for statements. ANALYZE statement is a new command which collects and displays data about statement execution. In this talk, I will show how one can use it to diagnose query optimization issues. I'll take poor-performing queries, run ANALYZE statement for them, and then show how one can identify the problem area. Then we'll take the fixed query, run ANALYZE statement again and see how the healthy output looks like. I'll also show how ANALYZE output looks like when the query is inherently expensive and cannot be improved through optimizer tuning. After the talk, you'll know how to quickly narrow down query optimization issues.

Speakers

Sergei Petrunia's picture

Sergei Petrunia

Query Optimizer Developer, MariaDB Corporation

Biography:

Sergei Petrunia is a query optimizer developer at MariaDB Corporation Ab. He implemented MariaDB's optimizer features like Table Elimination (MariaDB 5.1), improvements to semi-join subquery optimizations (MariaDB 5.3/5.5), SHOW EXPLAIN (MariaDB 10.0), and ANALYZE for statements (MariaDB 10.1) Prior to Monty Program, he has worked for MySQL Ab/Sun Microsystems, where he was the author of query optimizer features like Partition Pruning, Index Merge (MySQL 5.1), and numerous smaller improvements in query optimizer and other parts of the server. He maintains a highly technical blog at http://s.petrunia.net/blog

Share this talk