Submissions/Index?? Filesort?? An Introduction to MySQL Query Performance Optimization
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)
- Author of the submission
- Roan Kattouw
- E-mail address
- 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.
- Technology and Infrastructure
- Length of presentation/talk
- 25 minutes
- Will you attend Wikimania if your submission is not accepted?
- Slides or further information (optional)
- Special request as to time of presentations
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. (~~~~).