Expanding on the PHPNW08 talk “MySQL EXPLAIN Explained” delegates will gain an understanding of how to tackle slow queries in a practical setting. Although delegates will be expected to have some experience of writing MySQL queries, the tutorial can scale as the audience requires. We can detour into a MySQL select query master class covering joins, join conditions, functions (including aggregate functions) and more.
We’ll start off by covering how to identify slow queries and analysing them. By using Percona’s improved slow query log and then the MySQL EXPLAIN tool, delegates should be comfortable identifying performance bottlenecks.
The tutorial will also cover the main approaches to improving query (and server) performance: Query design, table design, summary fields/tables, indexing strategies, stored procedures and we will also touch upon tweaking MySQL’s configuration to yield best possible performance. Each area will cover the theory and then a practical example (where possible) to demonstrate understanding and the learning outcomes.
By the end of the tutorial, delegates should be comfortable in designing their own workflow (identification, analysis, problem replication, resolution) and have a set of strategies for mitigating the cause of slow performance in MySQL.
Questions and Answers
Who’s the target audience for this tutorial?
This talk is applicable to developers whose projects involve using MySQL (including MariaDB and Percona Server). If you have ever written a SELECT statement and been dissatisfied with the performance, whether it took 60 seconds or one second, this talk is for you. Most of the tutorial will be spent covering the approaches available to you as the developer, with a very small amount of time spent on configuring MySQL and the server itself.
How experienced do I need to be?
Delegates should be comfortable with basic relational database principles such as tables, indexes and sorting. However, many of the queries and data sets will be pre-made so no-one will be put on the spot for lack of query-writing experience. Most importantly, the principles and approaches apply from the ground up, so even delegates who aren’t currently dealing with an overloaded DB server can benefit from the session. As always, prevention is easier than cure, but curing slow queries is usually a lot more satisfying!
Which version of software / php etc will you be covering?
MySQL 5.5/5.6, but with a specific focus on the tools available for Percona Server – a drop-in replacement for MySQL.
Do I need to bring a laptop?
Ideally, yes, but this is not a requirement.
Do I need to install or set-up any specific software prior to the day, in order to get the most out of the day?
Tutorial delegates can either install something like MySQL Workbench beforehand, but as a bare minimum I will be providing web-based access to the example database sets.
What else will I get?
Refreshments and lunch, a personalised attendance certificate and a conference T-shirt!
Adrian Hardy
MySQL Query Optimisation
I’m a web developer based in the North West, UK and I work for Magma Digital Ltd as the Development Team Lead. I started using PHP commercially back in 2002 and I was fortunate enough that my first role also involved massive data sets where performance was critical. I was bitten by the query optimisation bug, and since that time I have always enjoyed working on applications with large data sets and making MySQL perform well.
