assert_efficient_sql

By Robert Fischer | Published: January 14, 2008

Okay, now that I’ve griped at MySQL a bit, I need to say something nice about it: the toolset support (at least on RoR) is second-to-none. As a great example, check out assert_efficient_sql, a plug-in which allows you to write unit tests and check for inefficient SQL queries. At this point, it mainly looks for things like returning too many rows and writing to the file system to do a sort, but the idea is cool.

One catch that jumps out right away is that you’re going to have to run this against a DB that looks a lot like production, since MySQL will punt to full table scans on smaller tables, and your unit test data probably qualifies as “smaller tables”.

The other catch is that it doesn’t detect an excessive amount of queries in order to implement a piece of functionality. The fact that you could merge three or four queries into one or two, or move queries around to minimize trips to the database — those are developer-level decisions that (I think) it would be hard to programaticaly detect. I dare you to prove me wrong: just be sure to open source the plugin proving it. :-D

Related posts:

  1. Is this a scam?
This entry was posted in To Be Categorized and tagged . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.
  • http://flea.sourceforge.net/PiglegToo_1.html Phlip

    Thanks for the tips – I’ll look at implementing them.

    About “an excessive amount of queries”, the general goal is you put the assertion around anything. I have put it around functional test post:action calls, for example. So the assertion should not guess how many different queries you should run in your production code, or how many different phases each one should run.

    About “punt to full table scans on smaller tables”, I will ask the MySQL internals mailing list if I can set their knob very low there!

  • http://www.linkedin.com/in/robertfischer Robert Fischer

    You probably don’t want to set the knob too low. It really is a lot faster to just do table scans than to use an index and followed by table look-ups in many cases. The particular count of rows depends on a lot of different factors (whether you have fixed-width or variable-width fields, whether you have nullable fields, the number of columns, various buffer sizes, etc., etc.), but my experience from performance tuning MySQL has generally shown that it’s a lot higher than I’d otherwise guess.

  • Categories