How I Used Query Analysis to Speed Up My Applications

The focus for this session is the different ways in which you can extract and use the information about queries to help identify and improve the quality of your queries and the structure of your database.

By using real applications and environments the session will detail how I have used different systems to examine the execution of my queries and how I’ve used combinations to help produce a more detailed report on your applications and queries.

Systems covered will included:

• Using EXPLAIN and how to use it effectively on groups of queries, rather than a single query, to identify problems in the table across a variety of queries, not just individual ones.

• The slow query log, and how to parse and report on the slow queries so that the key differences and deltas of slow queries are identified, rather than just the occasional query.

• The Query Analysis system within MySQL Enterprise which can provide reports and highlights for entire applications, individual servers, or both.

• MySQL Proxy allows you to monitor, record and extrapolate from queries and issues to help highlight and indicate problems. By collating details and combining the

• DTrace allows you to monitor queries on a running server, without altering or changing your application. By building using some simple techniques you can summarize or highlight problem queries in your application. By using a post-processing system in combination with DTrace you can build a more distinct picture of the issues.

For combinations, the session will examine how different tools can be used to provide different aspects of the problems with your query. For example, EXPLAIN on its own only demonstrates the potential problems with your query. But by combining EXPLAIN with DTrace you can gain a detailed understanding of which parts of the query process are taking the most time. By using this more specific information you can drill down and modify your queries, indexes and tables to achieve the best performance.

The focus will always be on improving the structure of the tables, queries and overall application logic, using the query analysis tools as a guide.