digg

Join DiggAbout

Discover the best of the web!
Learn more about Digg by taking the tour.

10 Tips for Optimizing MySQL Queries (That don’t suck)

20bits.com — When I read the last list of 10 tips for optimizing MySQL queries I wanted to scream. They were really bad. So, I spent an hour or so and wrote up my own list. I think it fares much better. What do you think?

Bury
show profanity settings
expand all
  • dgath, on 10/12/2007, -0/+3Great list! Reminds me of many of the tips Jay Pipes (who works for MySQL) gives people.
    • dante2010, on 10/12/2007, -11/+1Jay likes to hit the pipes.
    • gkzhang, on 10/12/2007, -0/+24Next article in the series: Ways To Optimize Your MySQL Queries That Kind of Suck But Not As Much As That Other Guy's On Digg + Ninjas
    • vann, on 10/12/2007, -3/+4Great idea! I'll start writing that tonight.
    • bejayel, on 10/12/2007, -0/+13Anyone else find it funny that his database is the first thing that crashed?
    • bejayel, on 10/12/2007, -6/+0omg. Really wish diggs login system actually worked. Digg me down.
    • rogueman, on 10/12/2007, -0/+2On the topic of profiling, my best find in a java environment was to keep track of all queries based on their stack trace. i.e. keep a Map with Exception.printStackTrace() as key and total run time as value. Then sort it and print the best 10 whenever I feel like it.
      The big advantage is that it gives me the point in the source code I have to edit, and not a certain query. I had quite a few surprises with this: small innocent queries were way to frequent or big queries that ran every two hours. With this I don't even bother with indexes or other stuff. Write first, optimize when necessary.

      This is something that I think can only be done in java. Do people in the PHP crowd know of a similar feature?


  • adidos, on 10/12/2007, -2/+12http://www.duggmirror.com/ caught it!
  • rodrigo74, on 10/12/2007, -9/+4Your tips might be good, but your web server sucks, it's dead already after 60 diggs.

    http://duggmirror.com
    • gorkish, on 10/12/2007, -1/+14Seems to be a database problem. Should we trust this dude?
    • apotropaic, on 10/12/2007, -0/+6Oh the irony!! Wordpress is so full of unoptimized queries and crashes the page thats supposed to display top 10 tips for optimizing queries!
  • ahsile, on 10/12/2007, -7/+2Your server blew up fast...
  • legendxx, on 10/12/2007, -0/+4yeah I have no idea why that last idiot was dug up so fast.
  • T0PS3O, on 10/12/2007, -0/+22Looks like Digg needs a 'This is a Response to...' ala YouTube!
  • nahun, on 10/12/2007, -3/+2looks like his site is up just fine.
  • vann, on 10/12/2007, -2/+6Haha. This is hosted on a server run by a friend of mine. Looks like it's time for me to invest in my own hardware. :)
    • rodrigo74, on 10/12/2007, -0/+5He might be really happy with you now.
    • sulf, on 10/12/2007, -2/+4You might want think about hardware and bandwidth next time _before_ submitting your article to digg again.
    • vann, on 10/12/2007, -1/+3Well, we weren't quite sure whether it could handle being dugg. Think of this as a field experiment.
  • dakke, on 10/12/2007, -5/+1#1 way to speed us mysql:
    --with-blackhole-storage-engine
  • wrongplanet, on 10/12/2007, -2/+14"Error establishing a database connection"

    how fitting
    • vann, on 10/12/2007, -3/+8Well, although I've lost a little face, I can say that I didn't configure this machine. Eh heh.
  • MeltingIce, on 10/12/2007, -4/+4Enough of these responses, there is only one thing that needs to be said about optimizing MySQL queries: the best way to optimize your database varies from project to project, so there are no top 10 ways to do it that encompass every project.
  • hmmmok, on 10/12/2007, -16/+4Best tips

    1) Use SQL Server
    2) Use Oracle
    3) Use Sybase
  • boheme, on 10/12/2007, -5/+0Hmmm...

    "WordPress
    Error establishing a database connection"


    Perhaps he needs to optimize his own MySQL methods before telling other people how to optimize theirs...

    -Chris Knight
  • Nachoes, on 10/12/2007, -1/+6I'm sick of blog posts showing up on the front page and when you click on the link you are presented an error page.
    • tehmoth, on 10/12/2007, -0/+7especially when they're blog posts about optimising databases.
  • prockcore, on 10/12/2007, -0/+11I've said it a million times. Wordpress has some of the most inept programming I've ever seen.

    It puts more stress on mysql than most blog software. (20 separate queries are used just to load a single post).

    You can blame the hardware all you want.. if this guy was using textpattern or some other software, he'd be able to handle 10 times the amount of traffic.
  • Kailash.Nadh, on 10/12/2007, -0/+4Optimize your WP queries first.
  • anastrophe, on 10/12/2007, -0/+3sheez, the 'funny how his database is down, how ironic' comment was funny (and not _that_ funny) when the first poster made it. can y'all stop playing carlos mencia for a few minutes?

    the biggest problem in this case is WORDPRESS, which is great blog software, poorly coded. these people can't even generate a unified diff to do updates for crying out loud - you have to install the entire new version on top of your current version, then go through a ridiculous number of contortions to make sure it all works right.

    but i digress.

    wordpress will fall over and play dead if you cross your eyes at it. best thing these folks could have done would to have been to install the WP-cache plugin so that the page wouldn't have to be generated on the fly for each new connection.

    but i digress again.

    • vann, on 10/12/2007, -0/+3wp-cache is enabled. Unfortunately the entire cache is invalidated whenever a new comment is made, so if you're in a high-traffic situation and someone makes a comment....kablooey.
    • anastrophe, on 10/12/2007, -0/+3ouch. i wasn't aware of that. i'll keep that in mind the next time i get around to writing a diggworthy article on my little blog, hosted on a 440mhz ultrasparc II with 1g memory. last time i got dugg (before i'd even heard of wp-cache), the site was effectively down for nearly 36 hours. sounds like wp-cache wouldn't have helped. eek!
  • vann, on 10/12/2007, -0/+2Ok, site's back up. Enjoy!
  • teddy2, on 10/12/2007, -0/+2The Mysql documentation recommends using as few parenthesis as possible. It says that extra parenthesis will slow down queries. I was surprised when I read this, but I guess if you have a lot of SQL queries to perform, the difference adds up to a noticeable/measurable time savings.

    I tried to find the reference to this, but I couldn't find it in less than a few minutes.

    The author disregarded this suggestion. I haven't done any bench testing to verify this, but I remember reading this.
    • vann, on 10/12/2007, -0/+2Hmm. Well, if that's the case then I'll admit I was wrong on that count. Still, that's not the first step one would take when optimizing your queries. Also, I'd want to see numbers. I'd bet that this is entering the territory of diminishing returns.
    • rdez6173, on 10/12/2007, -0/+2Here's the documentation: http://dev.mysql.com/doc/refman/5.0/en/where-optimizations.html

      Though I agree that the benefits are most likely negligible.
    • agnoster, on 10/12/2007, -0/+1Doesn't that documentation say it's one of the optimizations MySQL does internally? I.e. "don't worry dudes, we'll eliminate excess parentheses for you"?
  • beermad, on 10/12/2007, -0/+2Dugg for the useful suggestion about splitting tables with variable-length data.
    I now have a page built using blobs loading in 1.5 seconds instead of about 15.
  • Kblobz, on 10/12/2007, -2/+2if you had bothered to read the original, you would have known that the article was about optimizing queries. Most of yours were just generic ideas that work, but don't really have much to do with the title.

    you used this as an opportunity to get dugg...and nothing more.

    NO DIGG
    • vann, on 10/12/2007, -0/+3Mmm, those grapes are deliciously sour.

      But seriously, there's only so much you can do with the queries themselves. A query that runs well on Oracle might be awful on MySQL, which means understanding query performance is as much about understanding the characteristics of the underlying database as it is about the queries themselves. If you'll notice, I tried to divide the list into two parts: the first half are more general optimizations and the second half relate to queries specifically.
    • Kblobz, on 10/12/2007, -2/+1"Mmm, those grapes are deliciously sour."

      The ***** smell of your article is no substitute for grapes.

      "If you'll notice, I tried to divide the list into two parts: the first half are more general optimizations and the second half relate to queries specifically"

      Again, the orginal article was: "10 Tips for Optimizing MySQL Queries". Yours has little to do with optimizing queries and more about database optimization in general. I actually thought the original was pretty good and spot on when it comes to query optimization.

      You should spend less time posting comments on digg and more on actually coming up with a good article.
    • vann, on 10/12/2007, -0/+2Hmm, is it a coincidence that you've dugg every article posted from jlabs? I call shenanigans.

      Nice try, though. :)
    • vann, on 10/12/2007, -0/+2Also, it comes off like you're criticizing me for being *too* comprehensive. Haha. If that's the kind of criticism my blog posts engender, well, keep the insults coming!
  • dexterholland, on 10/12/2007, -0/+1I found a Google Tech Talk video that gives you mysql perfomance tips with examples - its pretty cool. Titled "Performance Tuning Best Practices for MySQL" http://blog.sherifmansour.com/?p=72
  • dexterholland, on 10/12/2007, -0/+2@dgath Jay Pipe's video is the one I am talking about. Watch it: http://blog.sherifmansour.com/?p=72
  • SilverRocket, on 10/12/2007, -0/+1While not a query tuning idea, a big missing element to all these articles that help you 'speed up' MySQL is turning on the MySQL Query Cache. This alone can be the one single optimization required to get your database as fast as the wind once again...
  • yogastore, on 06/27/2008, -0/+0http://astore.amazon.com/flowtron.mosquito-20
    http://astore.amazon.com/flowtron.insect.killer-20
    http://astore.amazon.com/evaporative.air.cooler-20
    http://astore.amazon.com/air.swamp.cooler-20
    http://astore.amazon.com/braun.electric.kettle-20
    http://astore.amazon.com/cordless.electric.kettle- ...
    http://astore.amazon.com/canon.battery.charger-20
    http://astore.amazon.com/12.volt.battery.charger-2 ...
    http://astore.amazon.com/furniture.chaise.lounge-2 ...
    http://astore.amazon.com/outdoor.chaise.lounge-20
  •  

    Login or register to add a comment

    Create a new account or login to join in the conversation on Digg. You'll also be able to Digg stories to help promote things you like.


© Digg Inc. 2008 — Content posted by Digg users is dedicated to the public domain.
DIGG, DIGG IT, DUGG, DIGG THIS, Digg graphics, logos, designs, page headers, button icons, scripts, and other service names are the trademarks of Digg Inc.