Query Optimization: From 0 to 10 (and up to 5.7)

Performance Optimization
21 September 9:00AM - 12:00PM @ Zürich 1

Experience level: 
6 hours tutorial
This tutorial is divided into 2 parts: The first part will be about query optimization basics, and is aimed to Developers and DBAs without requiring a deep understanding of MySQL Internals (applying to most MySQL versions from 5.5 to 5.6 and MariaDB 10): * Example database - Environment setup * Access types and basic indexing - How indexes work to speed-up queries - BTREE indexes - EXPLAIN - InnoDB peculiarities * Multi-column indexing - Column order - Index condition pushdown - Index_merge * FULLTEXT indexing - R-Tree properties * Join execution and optimization - Nested-loop - Other strategies and variants: MRR and hash joins * Subquery issues - Issues with subqueries in older versions - Semi-Join execution and other optimizations * Query profiling - Percona toolkit: Pt-query-digest and others - Using SYS and performance_schema to profile queries The second part requires previous experience with MySQL (or attending the first part) and will include the latest innovations on query optimization only found in MariaDB 10.1 and specially in Oracle MySQL 5.7: * Query rewriting API * New-cost based optimizer * EXPLAIN FOR CONNECTION/SHOW EXPLAIN FOR * Virtual columns * Configurable costs, innodb stat tables and histograms * New JSON native type * Subquery materialization, derived tables and temporary tables format * GIS and fulltext optimizations and new features * Optimizer hints and max_statement_execution * Other small bug fixes and features (UNION ALL, default sql_mode, optimizer_search_depth, ...) Predownload these files to be ready from minute 1! * Download the nlwiktionary example sql dump (.sql.bz2, 131 MB): https://docs.google.com/a/wikimedia.org/uc?export=download&confirm=ulnW&id=0BwSut9XMRwyJSlFHVFYzcHhGd2M" * Download the OpenStreetMap example sql dump (.sql.bz2, 127 MB): https://dl.dropboxusercontent.com/u/27217745/osm_backup.sql.bz2 Mirror: https://docs.google.com/a/jynus.com/uc?id=0B-wHYNV-Xb6kaGNHLVhMbk93MlE&export=download


Sr Database Administrator, Wikimedia Foundation
Jaime works as the sole database administrator for the non-profit organization Wikimedia Foundation, host of Wikipedia and other similar open knowledge projects.

His main topics of interest are MySQL query optimization and architectures for high availability.

He is a former MySQL A.B./Sun Microsystems/Oracle/Percona instructor and consultant, and has also worked as an independent MySQL consultant at DBAhire.com.