Tag Archives: optimization

3 Simple Patterns for Tighter MySQL Code

Join 8000 others and follow Sean Hull on twitter @hullsean.

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

Here’s 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 speedup 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 optimizer. Hoorah!

Also: 5 Things Deadly to Scalability.

2. Repair those UNIONs

If your code uses the UNION construct in SQL, there are a few different ways to tune those queries. You can use UNION ALL or pushdown conditions can help you optimize UNION in MySQL.

Read this: MySQL DBA Hiring Guide for candidates, managers & recruiters

4. Better PAGING through datasets

Does your web application display pages of users, pages of orders or pages of items? If you’re using
LIMIT and OFFSET there are 3 good ways to optimize these in MySQL.

Check out: Scalability Happiness – A Quiet Query Log

Get some in your inbox: Exclusive monthly Scalable Startups. We share tips and special content. Here’s a sample

3 Ways to Optimize for Paging in MySQL

Join 6100 others and follow Sean Hull on twitter @hullsean.

Lots and lots of web applications need to page through information. From customer records, to the albums in your itunes collection. So as web developers and architects, it’s important that we do all this efficiently.

Start by looking at how you’re fetching information from your MySQL database. We’ve outlined three ways to do just that.

Also check out: Five more things Deadly to Scalability.

1. Paging without discarding records

Ultimately we’re trying to avoid discarding records. After all if the server doesn’t fetch them, we save big. How else can we avoid this extra work.

How about remember the last name. For example:

[code]
select id, name, address, phone
FROM customers
WHERE id > 990
ORDER BY id LIMIT 10;
[/code]

Also: The Mythical MySQL DBA.

Of course such a solution would only work if you were paging by ID. If you page by name, it might get messier as there may be more than one person with the same name. If ID doesn’t work for your application, perhaps returning paged users by USERNAME might work. Those would be unique:

[code]
SELECT id, username
FROM customers
WHERE username > ‘shull@iheavy.com’
ORDER BY username LIMIT 10;
[/code]

Read this: Myth of Five Nines.

[quote]
Paging queries can be slow with SQL as they often involve the OFFSET keyword which instructs the server you only want a subset. However it typically scans collects and then discards those rows first. With deferred join or by maintaining a place or position column you can avoid this, and speedup your database dramatically.
[/quote]

2. Try using a Deferred Join

This is an interesting trick. Suppose you have pages of customers. Each page displays ten customers. The query will use LIMIT to get ten records, and OFFSET to skip all the previous page results. When you get to the 100th page, it’s doing LIMIT 10 OFFSET 990. So the server has to go and read all those records, then discard them.

Also: AirBNB didn’t have to fail during an AWS outage.

[code]
SELECT id, name, address, phone FROM customers ORDER BY name LIMIT 10 OFFSET 990;
[/code]

MySQL is first scanning an index then retrieving rows in the table by primary key id. So it’s doing double lookups and so forth. Turns out you can make this faster with a tricky thing called a deferred join.

The inside piece just uses the primary key. An explain plan shows us “using index” which we love!

[code]
SELECT id
FROM customers
ORDER BY name
LIMIT 10 OFFSET 990;
[/code]

Now combine this using an INNER JOIN to get the ten rows and data you want:

[code]
SELECT id, name, address, phone
FROM customers
INNER JOIN (
SELECT id
FROM customers
ORDER BY name
LIMIT 10 OFFSET 990)
AS my_results USING(id);
[/code]

That’s pretty cool!

3. Maintain a Page or Place column

Another way to trick the optimizer from retrieving rows it doesn’t need is to maintain a column for the page, place or position. Yes you need to update that column whenever you (a) INSERT a row (b) DELETE a row ( c) move a row with UPDATE. This could get messy with page, but a straight place or position might work easier.

[code]
SELECT id, name, address, phone
FROM customers
WHERE page = 100
ORDER BY name;
[/code]

Hiring? MySQL DBA Interview Guide for Candidates & Managers.

Or with place column something like this:

[code]
SELECT id, name, address, phone
FROM customers
WHERE place BETWEEN 990 AND 999
ORDER BY name;
[/code]

Get some in your inbox: Exclusive monthly Scalable Startups. We share tips and special content. Here’s a sample

How to Optimize MySQL UNION For High Speed

obama innauguration big data sets

Join 6100 others and follow Sean Hull on twitter @hullsean.

There are two ways to speedup UNIONs in a MySQL database. First use UNION ALL if at all possible, and second try to push down your conditions.

[mytweetlinks]

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:

[code]
blue
green
gray
black
[/code]

And long_sleeve another that looks like this:

[code]
red
green
yellow
blue
[/code]

Related: Why Generalists are Better at Scaling the Web

If you UNION those two tables, first MySQL will sort the combined set into a temp table like this:

[code]
black
blue
blue
gray
green
green
red
yellow
[/code]

Once it’s done this sort, it can easily remove the duplicate blue & duplicate green for this resulting set:

[code]
black
blue
gray
green
red
yellow
[/code]

See also: Mythical MySQL DBA – the talent drought.

Why does it do this? UNION is defined that way in SQL. Duplicates must be removed and this is an efficient way for the MySQL engine to remove them. Combine results, sort, remove duplicates and return the set.

[quote]
Queries with UNION can be accelerated in two ways. Switch to UNION ALL or try to push ORDER BY, LIMIT and WHERE conditions inside each subquery. You’ll be glad you did!
[/quote]

What if we did UNION ALL? The result would look like this:

[code]
blue
green
gray
black
red
green
yellow
blue
[/code]

Read this: MySQL DBA Interview & Hiring Guide.

It doesn’t have to sort, and doesn’t have to remove duplicates. If you imagine combining two 10 million row tables, and don’t have to sort, this speedup can be HUGE.

2. Use Push-down Conditions to speedup UNION in MySQL

Imagine with our example above the shirts have a design date, the year they were released. Yes we’re keeping this example very simple to illustrate the concept.

Here is the short_sleeve table:
[code]
blue 2013
green 2013
green 2012
gray 2011
black 2009
black 2011
[/code]

And long_sleeve table looks like this:

[code]
red 2012
red 2013
green 2011
yellow 2010
blue 2011
[/code]

For 2013 designs could combine them like this:

[code]
(SELECT type, release FROM short_sleeve)
UNION
(SELECT type, release FROM long_sleeve);
WHERE release >=2013;
[/code]

See also: 5 More Things Deadly to Scalability and the original 5 Things Toxic to Scalability..

Here the WHERE clause works on this 11 record temp table:

[code]
black 2009
black 2011
blue 2011
blue 2013
gray 2011
green 2013
green 2012
green 2011
red 2012
red 2013
yellow 2010
[/code]

But it would be much faster to move the WHERE inside each subquery like this:

[code]
(SELECT type, release FROM short_sleeve WHERE release >=2013)
UNION
(SELECT type, release FROM long_sleeve WHERE release >=2013);
[/code]

That would be operating on a combined 3 record table. Faster to sort & remove duplicates. Smaller result sets cache better too, providing a pay forward dividend. That’s what performance optimization is all about!

Read this: RDS or MySQL – 10 Use Cases.

Remember multi-million row sets in each part of this query will quickly illustrate the optimization. We’re using very small results to make visualizing easier.

You can also use this optimization for ORDER BY and for LIMIT conditions. By reducing the number of records returned by EACH PART of the UNION, you reduce the work that happens at the stage where they are all combined.

If you’re seeing some UNION queries in your slow query log, I suggest you try this optimization out and see if you can tweak

Get more. Grab our exclusive monthly Scalable Startups. We share tips and special content. Here’s a sample

A Pagerank of 5 Is Possible – Here's How

Join 4500 others and follow Sean Hull on twitter @hullsean.

A highly trafficked website is a valuable asset indeed. For a services business it helps you build reputation and reach prospects.

Here’s how to get there.

1. Longevity

We’ve been around for a while, as you can see from a quick whois search below. I’ve owned the web property (aka domain name) iheavy.com and used it for the same purpose, since July 1999! Google notices this and ranks accordingly.

Until 2011 I wasn’t blogging much. I had a pagerank of 3 though. That’s attributable to two factors:

o 12 years owning the domain at that time

o Writing a book for O’Reilly which got a strong backlink

[code]
$ whois iheavy.com

Registered through: GoDaddy.com, LLC (http://www.godaddy.com)
Domain Name: IHEAVY.COM
Created on: 14-Jul-99
Expires on: 14-Jul-15
Last Updated on: 18-Feb-13

Registrant:
iHeavy, Inc.
Box 5352
New York, New York 10185
United States

Administrative Contact:
Hull, Sean hullsean@gmail.com
iHeavy, Inc.
Box 5352
New York, New York 10185
United States
+1.2125336828
[/code]

2. Authored a Technical Book (pagerank 3)

I authored a book for O’Reilly in 2001 called Oracle and Open Source. This bumped up our ranking from a flat 1 because we got backlinks from O’Reilly’s author blog, a strong authoritative signal to Google.

Why is database administration talent in short supply? They are the Mythical MySQL DBA

Here’s Why I Wrote the Book on Oracle & Open Source.

[quote]
Consistent ownership and use of a domain name, along with backlinks from other authorities in your area of expertise weigh strongly in your favor.
[/quote]

3. Started blogging weekly

In Spring of 2011 I started blogging regularly. This was an effort to build out my services business, solidify my voice, and bring prospects and customers to my site.

[mytweetlinks]

4. Installed Google Analytics & Feedburner

It might seem crazy but to that point I didn’t track much. Without metrics you don’t know which pages users are visiting, how long they’re staying, or where they’re converting.

Also take a look at: Why Generalists are Better at Scaling the Web

A conversion – for those out of the analytics loop – is when a user does something you want them to do. For an e-commerce site, they buy or start the process of buying. For a services website it could be visiting your about page, downloading a pdf or e-book, or signing up for a newsletter.

5. WordPress SEO plugin

WordPress is a great publishing platform. Among the many plugins to choose from, Yoast SEO is a very important one to include. It exposes all the hidden SEO fields and functions in a powerful way. Edit your short description, keywords & categories, and a lot more.

Check out: A CTO Must Never Do This

It also helps you frame and think about how your content is seen both by search engines, and searchers alike.

6. Keyword research

A little keyword research goes a long way. You might be a subject matter expert in a given field, but if you don’t know how your customers search, you can’t help them find you. Remember they don’t know what you do, so likely don’t know jargony terms or the vernacular your expertise uses within.

SEO Moz has some great tools to help you, along with Wordtracker and Google has a keyword research tool for adwords.

[quote]
Strong titles should make you click to open the post. A dash of keyword research and regularly watching your analytics should be revealing. Give your readers what they want!
[/quote]

See also: My Blog Traffic is Growing Using these 5 Killer Tactics

7. Watch your analytics (pagerank 4)

After about six months of regular blogging, and a few viral hits, our pagerank went up to 4. What was I doing? All of the above, plus watching analytics closely. I asked myself questions about visitors:

o Which pages do they like and why?
o What causes them to stick around?
o What causes bounce rate to go down?
o What causes them to convert?

I found that adding links to relevant content right in the text helped reduce bounce rate right away. This was a real discovery that I could apply everyday.

Hiring a Cloud Engineer? Get our 8 Questions to Ask an AWS Expert for Recruiters, Managers & candidates alike

I also noticed that good content helped, but directly imploring readers to signup to the newsletter got regular conversions daily. Huh, that was a surprise since all along I had the signup form along the right column. Go figure.

8. Guest posting

Guest posting is great. It allows you to work with real publications who have paid editors. These folks with provide you with a more professional view, and that is great for your own writing and understanding your audience. The hardest thing to learn is how to write to a broad audience.

You’ll also of course get a backlink which is a major authority signal to the search engines. You might get paid a bit too, but your mileage may very.

I managed to do some regular writing for INFOWORLD and Database Journal. I wrote one piece for ChangeThis.com called Get Out of the Technology Hex.

From there I signed a syndication deal with Developer Zone. Since I have embedded links to content, that brings me regular traffic, even besides my profile, and the authoritative backlink.

Lately I’m working on some stuff for Gigaom and ACM’s Queue. Steady as she goes!

9. Get on the aggregators

Most likely your industry has some sort of aggregator site which will carry your RSS syndication feed. Get on those. That will drive regular traffic and RSS feedburner subscribers. We’re on Planet MySQL and it’s been great!

10. Patience, rinse and repeat

Easier said than done, I know. If you want this to happen overnight, you had better get onto the real world celebrity track. Otherwise work on your content, work on your voice, write clicky titles and keep your audience interested with solid content. And watch your traffic grow!

Get more. Grab our exclusive monthly Scalable Startups. We share tips and special content. Here’s a sample

MySQL Optimization

Experiencing performance problems with your MySQL 4.x or 5.x database? We can help. We have over a decade of experience tuning sophisticated multi-tier web applications. This allows us to identify and pinpoint problems quickly, drilldown to the relevant application and SQL, and sift out the problem code.

All this makes your applications run faster, and your customers happy!

call_quote