Effective MySQL: Optimizing SQL Statements Book Review

Practical Knowledge for Performance Improvement

Optimizing SQL Statements by Ronald Bradford is a nice sized pocket type of book with 165 pages in it. What I like it about it, is that it’s really easy to read and provides you with just the right amount of information you need if you are have problem MySQL SQL statements running on your server.

There are 9 chapters in the book, the first one is called the five minute DBA. This chapter in just 7pages tells you the first steps to look at when you have problem SQL statements. This includes finding the problem SQL and then what to do with it.

The second chapter walks the reader through the essential MySQL commands you need to know in order to fix your slow SQL statements. E.g. SHOW INDEXES, SHOW VARIABLES and EXPLAIN plan statements.

Chapter three takes you through how indexes work and the types you can apply, just before chapter four gets you to create your indexes. This chapter covers using indexes for pattern matching, multi-column indexes and joining tables with indexes.

Chapter five covers a small subsection of the MySQL database system variables that can affect your indexes and the speed of your SQL statements. This chapter looks at key_buffer_size, innodb_buffer_pool_size, query_cache_size, tmp_table_size and sort_buffer_size among others.

Chapter 7 looks at the SQL lifecycle. This chapter allows you to find out how to find your slow running SQL, then how to confirm that they are problematic and then how to go about fixing them. This involves looking at various logs, including the general logs, the slow query log and the processlist.

Chapter 8 is a very interesting chapter entitled ‘Hidden Performance Tips’. This provides lots of useful tips such as combining your DDL, removing duplicate indexes, removing unused indexes, caching results, simplifying SQL statements and choosing the correct datatype in the table definitions.

The final chapter is all about explaining the EXPLAIN plan command in MySQL. This provides the reader with the correct EXPLAIN syntax to use and how to read the results of the explain plan.

Overall, I really love this little book. Instead of having to read through a book of 400-500 pages to find the section on what to do if your MySQL statements are slow, just reach for this concise book which should answer all your questions about speeding up your SQL statements.

This is a particularly useful book if you have full access to your MySQL database or if you just use a hosted version of MySQL from a provider. Thoroughly recommended.