Optimizing SQL Statements
The first book of the Effective MySQL series provides the reader with the tools, knowledge and tips necessary for optimizing SQL Statements in MySQL.
Written by Oracle ACE Director and MySQL expert Ronald Bradford, Effective MySQL: Optimizing SQL Statements is filled with detailed explanations and practical examples that can be applied immediately to improve database and application performances. Featuring a step-by-step approach to SQL optimization, this Oracle Press book helps you to analyze and tune problematic SQL statements.
- Identify the essential analysis commands for gathering and diagnosing issues
- Learn how different index theories are applied and represented in MySQL
- Plan and execute informed SQL optimizations
- Create MySQL indexes to improve query performance
- much more…
Chapters
- The Five Minute DBA – Code Examples – Download Sample Chapter (PDF)
- The Essential Analysis Commands – Code Examples
- Understanding MySQL Indexes – Code Examples
- Creating Indexes – Code Examples
- Creating Better Indexes – Code Examples
- MySQL Configuration Options – Code Examples
- The SQL Lifecycle – Code Examples
- Hidden Performance Tips – Code Examples
- Explaining the MySQL Explain – Code Examples
Buy Now
This title is available in print, and ebook formats from these retailers.
 |
 |
 |
Get the Code
In addition to the individual chapter links provided above you can download all SQL examples via github with the following command.
$ git clone git://github.com/effectiveMySQL/OptimizingSQLStatements.git
Other Titles
See other titles in our series including:
- Effective MySQL: Backup and Recovery
- Effective MySQL: Advanced Replication Techniques
Related Presentations
You may also like these related presentations:
MySQL Backup & Recovery Essentials
A hardware, software or human failure can occur at any time. Are you prepared?
Many organizations take a risk of serious data loss and system downtime with inadequate procedures in place to support a disaster recovery. This presentation covers the essentials of MySQL backup and recovery options, identifying the necessary tools for an effective strategy to support data resilience and business continuity for your organization. MySQL has no one single unbreakable backup solution, so it is important to understand the impact of MySQL replication, storage engines, configuration options for durability, hardware configuration and the impact on locking and uptime for the various hot/warm/cold options available.
Short Url: http://j.mp/EM-BandR
Presenter: Ronald Bradford
Schedule: RMOUG QEW – May 2012- Denver, Colorado
LAMP Performance Optimizations – Volume 1
Get a detailed introduction to the very broad topic of end-to-end performance optimization in web applications based on LAMP stack (Linux, Apache, MySQL, PHP).
Short Url: http://j.mp/EM-LAMP-opt
Presenter: Ronald Bradford
Schedule:
- OTN MySQL Developer Day in May 2012 – New York.
- Effective MySQL Meetup York and Web Performance Meetup. – July 2011 New York
Setting up Google Cloud SQL
You can activate Google Cloud SQL from the Google APIs Console at https://code.google.com/apis/console. NOTE: At the time of this publication this was in limited beta.
From the Google APIs console, you can create a new instance. You can then use the Web interface to run SQL statements, import and export data. There is also a Google SQL command line client that can be configured with:
# Java 6 is a dependency of the Google SQL client $ [ -z `which java 2>/dev/null` ] && sudo apt-get install -y openjdk-6-jre-headless $ [ -z `which unzip 2>/dev/null` ] && sudo apt-get install -y unzip $ cd $HOME $ mkdir cloud-sql $ cd cloud-sql/ $ wget http://dl.google.com/cloudsql/tools/google_sql_tool.zip $ unzip ../google_sql_tool.zip $ ./google_sql.shPlease authorize Google SQL Service for your Google Account at: http://goo.gl/XXXX Enter Authorization Code: ************************* sql> show schemas; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.10 sec)
NOTE: The *first time* you access your instance you need to confirm authorization by going to the provided URL.
Setting up AWS RDS
Amazon Web Services (AWS) provides a managed MySQL solution via Relational Database Service (RDS). The following instructions will enable you to configure and run RDS. Refer to Using Amazon Web Services for initial information about AWS requirements.
The following instructions are for Ubuntu.
Pre-requisites
$ [ -z `which java 2>/dev/null` ] && sudo apt-get install -y openjdk-6-jre-headless $ [ -z `which unzip 2>/dev/null` ] && sudo apt-get install -y unzip
Installation
# See http://docs.amazonwebservices.com/AmazonRDS/latest/CommandLineReference/Welcome.html?r=8890 $ cd $HOME $ mkdir -p aws $ cd aws $ wget http://s3.amazonaws.com/rds-downloads/RDSCli.zip $ unzip RDSCli.zip $ ln -s RDSCli-*/ rds
Configuration
You need to create the $HOME/aws/credentials file with your specific account details.
AWSAccessKeyId=XXXXXXXXXXXXXXXXX AWSSecretKey=YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY
The following should be added to a given startup profile
export JAVA_HOME=/usr/lib/jvm/java-6-openjdk/jre export AWS_RDS_HOME=$HOME/aws/rds export PATH=$AWS_RDS_HOME/bin:$PATH export AWS_CREDENTIAL_FILE=$HOME/aws/credentials
A number of different MySQL versions are available.
$ rds-describe-db-engine-versions -e mysql VERSION mysql 5.1.45 mysql5.1 MySQL Community Edition Mysql 5.1.45 VERSION mysql 5.1.49 mysql5.1 MySQL Community Edition MySQL 5.1.49-R1 with innodb plugin VERSION mysql 5.1.50 mysql5.1 MySQL Community Edition MySQL 5.1.50-R3 VERSION mysql 5.1.57 mysql5.1 MySQL Community Edition MySQL 5.1.57-R1 VERSION mysql 5.1.61 mysql5.1 MySQL Community Edition MySQL 5.1.61-R1 VERSION mysql 5.5.12 mysql5.5 MySQL Community Edition MySQL 5.5.12-R1 VERSION mysql 5.5.20 mysql5.5 MySQL Community Edition MySQL 5.5.20-R1 VERSION mysql 5.5.8 mysql5.5 MySQL Community Edition MySQL 5.5.8.R1 GA
Verification
$ rds-describe-db-instances $ rds-describe-db-parameter-groups DBPARAMETERGROUP default.mysql5.5 mysql5.5 Default parameter group for mysql5.5
It is not possible to change the default parameter group so you need to create a copy for any future configuration modifications.
$ rds-create-db-parameter-group running-mysql55 -d 'Running MySQL 5.5' -f mysql5.5
Creating a new instance
$ rds-create-db-instance book2 --db-instance-class db.m1.large --allocated-storage 20 --engine mysql --master-username dba --master-user-password passwd --db-parameter-group-name running-mysql55
DBINSTANCE book2 db.m1.large mysql 20 dba creating 1 **** n 5.5.20 general-public-license
SECGROUP default active
PARAMGRP running-mysql55 in-sync
$ rds-describe-db-instances
DBINSTANCE book2 db.m1.large mysql 20 dba creating us-east-1a 1 **** n 5.5.20 general-public-license
SECGROUP default active
PARAMGRP running-mysql55 in-sync
It can take some time (e.g. 5-10 minutes for your instance to be available. When ready it will show available. In this case, the instance host is defined, however the instance is not yet accessible.
$ rds-describe-db-instances
DBINSTANCE book2 2012-04-18T15:09:08.420Z db.m1.large mysql 20 dba backing-up book2.cqmcgpjfkies.us-east-1.rds.amazonaws.com 3306 us-east-1a 1 n 5.5.20 general-public-license
SECGROUP default active
PARAMGRP running-mysql55 in-sync
While waiting, you can create the necessary permissions to access the RDS instance.
$ rds-describe-db-security-groups
SECGROUP default default
$ rds-authorize-db-security-group-ingress default --cidr-ip 10.194.163.1/21
SECGROUP default default
IP-RANGE 10.194.163.1/21 authorizing
$ rds-describe-db-security-groups
SECGROUP default default
IP-RANGE 10.194.163.1/21 authorized
$ rds-describe-db-instances
DBINSTANCE book2 2012-04-18T15:09:08.420Z db.m1.large mysql 20 dba available book2.cqmcgpjfkies.us-east-1.rds.amazonaws.com 3306 us-east-1a 1 n 5.5.20 general-public-license
SECGROUP default active
PARAMGRP running-mysql55 in-sync
The following can be used to monitor.
$ while [ : ]; do date; rds-describe-db-instances; sleep 15; done
Wed Apr 18 19:27:14 UTC 2012
DBINSTANCE book2 db.m1.large mysql 20 dba creating 1 **** n 5.5.20 general-public-license
SECGROUP default active
PARAMGRP running-mysql55 in-sync
...
Wed Apr 18 19:35:48 UTC 2012
DBINSTANCE book2 2012-04-18T19:31:51.919Z db.m1.large mysql 20 dba available book2.cqmcgpjfkies.us-east-1.rds.amazonaws.com 3306 us-east-1c 1 n 5.5.20 general-public-license
SECGROUP default active
PARAMGRP running-mysql55 in-sync
Confirmation of running MySQL Instance.
$ mysql -udba -ppasswd -hbook2.cqmcgpjfkies.us-east-1.rds.amazonaws.com -e "SELECT VERSION()" +------------+ | VERSION() | +------------+ | 5.5.20-log | +------------+
Terminating
$ rds-delete-db-instance book2 --skip-final-snapshot
Once you begin deleting this database, it will no longer be able to accept
connections.
Are you sure you want to delete this database? [Ny]y
DBINSTANCE book2 2012-04-18T14:58:54.251Z db.m1.large mysql 20 dba deleting book2.cqmcgpjfkies.us-east-1.rds.amazonaws.com 3306 us-east-1a 1 n 5.5.20 general-public-license
SECGROUP default active
PARAMGRP default.mysql5.5 in-sync
You can pass -f to avoid the prompt.
MySQL Idiosyncrasies That Bite
While MySQL is a popular and widely used database product, there are some default features and settings which can be foreign in comparison with other commercial RDBMS products such as Oracle. In this discussion, Ronald Bradford will discuss some of the MySQL defaults that are not what you may expect. These include understanding the concept of storage engines, and the default non-transactional state, how silent data truncations occur which affect your data, ideal practices for date management, and the MySQL transaction isolation options. These are all critical to clearly understand and implement correctly for data integrity and consistency. He will cover in-depth topics including SQL_MODE and the recommended best practices for default settings, the ideal user permissions and privileges including not what to do, and also the best practices for character sets and collations to ensure your UTF8 is stored and retrieved correctly.
Presenter: Ronald Bradford
Schedule:
Percona Live – Santa Clara, April 2012
New York MySQL Meetup, April 2011, New York
Explaining the MySQL EXPLAIN
Determining the Query Execution Plan (QEP) of an SQL statement is the primary analysis tool for DBAs. Understanding how to interpret the information from the EXPLAIN command and what additional commands and tools exist to add supplementary information are essential skills that will be used daily in production operations.
The MySQL EXPLAIN QEP is significantly different from an Oracle QEP and Oracle DBAs need to understand and learn the most appropriate way to understand and navigate this information to effective performance tune a running MySQL environment.
In this presentation we will cover the following:
- EXPLAIN & EXPLAIN EXTENDED Syntax
- Additional commands including SHOW CREATE TABLE, SHOW INDEXES and ANALYZE TABLE
- MySQL Primary Key, Unique Index, different Index Types and Index Cardinality specifics
- Understand how different Storage Engines affect the QEP
- The impact of multi-column indexes, partial indexes and covering indexes
Presenter: Ronald Bradford
Schedule:
Percona Live – Santa Clara, April 2012
EMEA Harmony – Helsinki, Finland May 2011
