Submissions/Index?? Filesort?? An Introduction to MySQL Query Performance Optimization

From Wikimania 2012 • Washington, D.C., USA

This is a rejected submission for Wikimania 2012.

Submission no. 662
Title of the submission
Index?? Filesort?? An Introduction to MySQL Query Performance Optimization
Type of submission (workshop, tutorial, panel, presentation)
Presentation
Author of the submission
Roan Kattouw
E-mail address
rkattouw@wikimedia.org
Username
User:Catrope
Country of origin
From the Netherlands, living in the US
Affiliation, if any (organization, company etc.)
Wikimedia Foundation
Personal homepage or blog
Abstract (at least 300 words to describe your proposal)

To many MediaWiki developers, SQL query performance and optimization is shrouded in mystery. Most know that there are efficient and inefficient queries, and that if they write an inefficient query, it will either be noticed during code review, or it will be noticed because it takes down a wiki, which will prompt an ops person to fix the breakage and yell at the developer that caused it. But few people seem to really understand how query performance works. How can you tell if a query is inefficient? How do you write efficient queries, and avoid inefficient ones? If so few people know this, it must be this difficult, mysterious thing, right? Fortunately, you don't have to be Domas or Tim to understand this. If you understand how a phone book works, you can learn this too.

In this presentation I will discuss the basics of how database engines in general, and MySQL specifically, execute different kinds of queries, and explain why certain queries are executed more efficiently than others and what role indexes play in this process. I will talk about best practices for writing efficient queries, how to design your tables and indexes so they facilitate efficient queries, and common pitfalls that result in inefficient queries and how to address them. I will also demonstrate how to obtain a query analysis from MySQL and how to make sense of it.

This will be a technical presentation primarily aimed at MediaWiki core and extension developers who want to avoid writing bad queries and reviewers that want to catch them. However, only basic knowledge of SQL is needed to understand the presentation; nothing in this presentation is specific to MediaWiki, and people who know nothing about things like PHP, MediaWiki internals or server-side database tweaking should be able to follow just fine.

Track
Technology and Infrastructure
Length of presentation/talk
25 minutes
Will you attend Wikimania if your submission is not accepted?
Yes
Slides or further information (optional)
Special request as to time of presentations


Interested attendees

If you are interested in attending this session, please sign with your username below. This will help reviewers to decide which sessions are of high interest. Sign with four tildes. (~~~~).

  1. -- DQ (ʞlɐʇ) 17:49, 21 March 2012 (UTC)[reply]
  2. Varnent (talk) 03:14, 1 April 2012 (UTC)[reply]
  3. Add your username here.