SQL is derided by many and for good reason. It’s key to scalability yet terribly difficult to write good code.

You can write tighter queries in MySQL easily by following 3 simple tricks –

SUBQUERIES, UNION, and PAGING.

In this article, we will explain all these patterns to write MySQL codes more efficiently.

3 Best and Simple Ways for Tighter MySQL Code

Here are a few quick tips to write tighter queries in MySQL:

1. Get Rid of Those Subqueries!

Subqueries are a standard part of SQL, unfortunately, MySQL doesn’t handle them very well. Luckily there’s a sweet rewrite that can put you in the fast lane. Here’s how to speed up a MySQL subquery by rewriting as a join.

Note that another compelling reason to upgrade to MySQL 5.6 is that this tweak has been rolled into the …

MySQL replication technology is powerful and flexible. But it doesn’t do everything perfectly all the time. You may experience trouble with the slaves falling behind the master or want to scale horizontally by building new slaves automatically. Or you might need to build a slave without blocking the master database.

All of these goals can be achieved using some powerful tools. Here’s a quick guide to those tools and how to use them.

5 Ways To Fortify MySQL Replication

Here are all the best 5 ways to fortify MySQL replications:

1. Build New Replicas Without Downtime

Something we’re sure you need to do quite often is to build new slaves. You can snapshot from another slave by bringing the slave down, copying its datadir to an alternate machine, updating the server_id and then starting up. However sometimes you have no slave, or your current slaves are serving data.

In those cases, …

So, you’ve been tasked with managing the MySQL databases in your environment, but you’re not sure where to start.

Here’s the quick & dirty guide. Oh yeah, and for those who love our stuff, take a look to your right.

See that subscribe button? Grab our newsletter!

Steps to MySQL Management

Here are the steps that are required for MySQL management as a DBA:

1. Installation

The “yum” tool is your friend.  If you’re using Debian, you’ll use apt-get but it’s very similar. You can do a “yum list” to see what packages are available. We prefer to use the Percona distribution of MySQL.

It’s fully compatible with stock MySQL distribution, but usually a bit ahead in terms of tweaks and fixes. Also, if you’re not sure, go with MySQL 5.5 for new …

There are a lot of considerations for deploying MySQL in the Cloud. Some concepts and details won’t be obvious to DBAs used to deploying on traditional servers.

For this, we have provided eight best practices in this guide that will certainly set you off on the right foot. Let’s explore them below.

This article is part of a multi-part series Intro to EC2 Cloud Deployments.

8 Practices You Should Follow While Deploying MySQL on Amazon

Here are eight best practices that you must follow while deploying MySQL on Amazon:

1. Replication

Master-Slave replication is easy to setup and provides a hot online copy of your data.  One or more slaves can also be used for scaling your database tier horizontally.

Master-Master active/passive replication can also be used to bring …

There is no need to mention that speed is one of the most sought-after attributes in any database system. Optimizing MySQL for high speed is essential to ensure responsive applications and efficient data processing.

There are basically two best ways to speed up UNION in MySQL – using UNION ALL and using push-down conditions.

In this article, we’ll explore techniques to achieve optimal performance and speed in MySQL.

Ways to Optimize MySQL for High Speed

Here’s a rundown of how to do those optimizations.

1. UNION ALL is much faster than UNION

How does a UNION work? Imagine you have two tables for shirts. The short_sleeve table looks like this:

And long_sleeve another that looks like this:

In today’s data-driven world, ensuring the safety of your valuable data is crucial.

To safeguard this data using MySQL, implementing a robust backup strategy is essential.

For this, we have mentioned ten important things to consider while dealing with MySQL backups. Let’s learn them in detail below.

10 Things You Must Do While Backing Up MySQL

Here are ten important considerations to keep in mind when dealing with MySQL backups.

1. Use Hot Backups

Hot backups are an excellent way to back up MySQL. They can run without blocking your application, and save tons of restore time. Percona’s xtrabackup tool is a great way to do this.  We wrote a how-to on using xtrabackup for hotbackups.

1. Use Logical Backups

Just because we …

You may already know that when the mysqldump client utility performs logical backups, it produces a set of SQL statements that you can execute to reproduce the original database object definitions and table data.

Although it dumps one or more MySQL databases for backup or transfer to another SQL server, there are 5 things that you may overlook with MySQL dumps.

Here I’ve discussed 5 things you shouldn’t overlook with MySQL dumps. Let’s explore them below.

5 Things You Need to Concentrate About MySQL Dumps

Here are the five things you may overlook with MySQL dumps:

1. Point In Time Recovery

If you’ve never done point in time recovery, it’s time to take a second look. With a standard mysqldump you restore your database to the time when the backup happened. Only do them once a day, then you can lose as much as 24 hours of data.

Enter point-in-time recovery, and you …

Autoscaling your webserver tier is typically straightforward. Image your Apache server with source code or without, then sync down files from S3 upon spinup. Roll that image into the autoscale configuration and you’re all set.

With the database tier though, things can be a bit tricky. The typical configuration we see is to have a single master database where your application writes. But scaling out or horizontally on Amazon EC2 should be as easy as adding more slaves, right? Why not automate that process?

Below we’ve set out to answer some of the questions you’re likely to face when setting up slaves against your master. We’ve included instructions on building an AMI that automatically spins up as a slave. Fancy!

1. How Can I Autoscale My Database Tier?

Step 1: Build an auto-starting MySQL slave against your master.

Step 2: Configure those to …

Amazon’s Relational Database Service is based on MySQL under the hood. So many colleagues and clients ask me – should I go with RDS or MySQL? As with every technology question, the answer is – it depends.

In this article, we are going to discuss 10 use cases of RDS or MySQL. Let’s get started below.

Ten Use Cases of RDS or MySQL

Here are some scenarios to help you decide:

1. I’m Replicating into Amazon from A Physical Datacenter

A: This setup is common if you’re using Amazon’s VPC or Virtual Private Cloud. With a router dropped into your datacenter, VPC allows you to extend and spinup virtual instances from Amazon as if they’re sitting in your own existing datacenter.

Great stuff, but you won’t be able to replicate from your existing master MySQL instance to cloud Amazon RDS instances. To do that, roll your own with MySQL 5.5 or Percona 5.5.  RDS can …

I’ve been getting more than my fair share of calls from recruiters of late. Even in this depressed economic climate where jobs are rarer than a cab at rush hour, it’s heartening to know that tech engineers are in great demand. And it’s even more heartening to think that the demand for MySQL DBAs has never been better.

In this article, I’m going to discuss the mythical MySQL DBA, the reason why MySQL DBA and target.com keep crashing and much more. So, let’s get started!

Why Target Keeps Crashing

My reckoning was confirmed by a Bloomberg news report about stalwart retailers suffering from a dearth of talented engineers. Bloomberg cited Target’s outage-prone e-commerce site as a symptom of, among other things the market’s shortage.

One of the challenges old-timers …