Optimize Your SQL With Percona’s Online Query Advisor!

Wouldn’t it be nice if you could get expert advice on your SQL queries to find problems in them, the same way that programmers can use lint-check tools to warn about bugs in their C?

Such a simple mistake, but it’s the kind of thing that James Bond movies are made of, isn’t it? Well, a lot of SQL queries have similar bugs, and thanks to Miguel Trias, now there’s a tool to help you find them. This is the second addition to our online suite of tools for MySQL users. You paste a query, it tells you what’s wrong with it. Simple as that.

Find the bug in this query:

Do you see it? Congratulations! I’ve analyzed that query with the tool, and shared the results with you. Click here to see if you were right.

That demonstrates another feature of the tool: you can share queries so they’re public, and other people can see them. The tool uses the same account that you might have already created for our online MySQL Configuration Wizard, which helps you choose good settings for your server.

Give it a try, and tell your friends! Enjoy hunting for bugs and problems in your SQL queries!

Share this post

Comments (24)

  • Derek Downey Reply

    That’s a pretty neat tool. Any plans to be able to submit a batch of queries for analysis (controlled via an API key)?

    February 15, 2012 at 11:21 am
  • Tyler Reply

    Nice tool. I missed the quoting around the date but this is a trade-off since I caught the fact that the query is also doing a full join of t1 to records in t2 with created_date < '2012-02-15' which is probably also a logic error.

    February 15, 2012 at 11:30 am
  • Tyler Reply

    Darn, I wish I could delete that comment, its “using” not use (facepalm)

    February 15, 2012 at 11:33 am
  • Baron Schwartz Reply

    Yeah, a lot of people don’t know the USING() syntax, but it is a nice shortcut. Shortcuts that are visually confusing are one of the biggest reasons to use a tool like this. What’s wrong with THIS query?

    select tbl1.* tbl2 from tbl1 join tbl2…..

    It aliases the last column from tbl1 and renames it “tbl2”.

    That’s exactly the kind of thing I’ve been noting down for the last few years as I solve query problems for clients, and built into this tool.

    February 15, 2012 at 11:39 am
  • Baron Schwartz Reply

    Derek, that’s an idea, isn’t it 😉

    February 15, 2012 at 11:40 am
  • Steve Jackson Reply

    select id from (select I am not sure this tool works with sub-queries)

    Apparently I’m awesome… I guess its not working with sub-queries yet….

    February 15, 2012 at 11:41 am
  • Baron Schwartz Reply

    Sure it does, if you give it a REAL subquery 🙂

    select * from t1 where id in (select id from t2)

    The SQL parser is pretty sophisticated; it’s not just looking for a regular expression like “select….in(select…” or anything like that.

    February 15, 2012 at 11:48 am
  • Harrison Reply

    One common mistake that it didn’t catch:

    UPDATE tbl SET c1=4 AND c2=5 WHERE id = 1

    February 15, 2012 at 12:14 pm
  • Baron Schwartz Reply

    Harrison, that’s a good one. Thanks!

    February 15, 2012 at 12:27 pm
  • Brian Boatright Reply

    This is a great tool. Thanks!!!

    February 15, 2012 at 12:40 pm
  • Pat Reply

    I was really confused for a minute, and then I realized that the mysqlperformanceblog RSS feed must have a bug in its HTML escaping.

    I mean, isn’t it obvious where the bug in “select * from t1 left join t2 using(id) where t2.created_date” is? “t2.created_date” is not a condition! 🙂

    February 15, 2012 at 1:06 pm
  • Steve Jackson Reply

    Sorry to be a party pooper Baren… maybe I am not supposed to be finding bugs, or maybe I am just using it incorrectly…

    But shouldnt the query

    “id from something”

    produce a warning with this tool?

    February 15, 2012 at 1:31 pm
  • Robert Treat Reply

    Hey Baron, nice tool. This blog post and comments actually made me learn something about Postgres. 🙂 The query you gave about “select tbl1.* tbl2 from tbl1 join tbl2…..” when I looked at it, I thought, “that should through a parse error”, and was surprised to hear it doesn’t. So then I went to try it in postgres and did the following:

    pagila=# select actor.* film_actor from actor join film_actor using (actor_id) limit 1;
    actor_id | first_name | last_name | last_update
    1 | PENELOPE | GUINESS | 2006-02-15 09:34:33

    I was surprised it worked! But, I’d note that it doesn’t alias the column in Postgres. Subtle difference. The thing is, it should throw a parse error. I asked on irc and we found this line in the standard:

    an is only allowed for a , not for an or or (which has its own AS (x,y,z) syntax)

    there’s a bunch of different corner cases that crop up in this area too… so yeah, again, cool tool, I’m sure we will make use of it.

    February 15, 2012 at 2:25 pm
  • Baron Schwartz Reply

    It’s not an SQL query, so the tool has no opinion on it 🙂 This tool isn’t meant to find syntax errors in queries that wouldn’t parse correctly, but rather to point out constructs in legal SQL that might hold traps for the unwary. Mark Callaghan once blogged that the problem with SQL is that it lets people do bad things; this tool’s goal is to help uncover those things as much as possible with static inspection of the SQL (i.e. no knowledge of the table structures and so on).

    February 15, 2012 at 7:26 pm
  • Shlomi Noach Reply

    Nice tool!

    Some input:
    In the following query, there isn’t a problem with the GROUP BY, yet the advisor claims a “Non-deterministic GROUP BY”.

    The next query is invalid but the advisor fails to detect the error (mixture of aggregate and non-aggregate).
    I’m not sure, based on your description, that the advisor is supposed to catch that (is it only supposed to work with queries that are valid?)

    In the following there is nothing wrong syntactically, but the author of the query might be confused to think that DISTINCT is a function, and that it only works for the ‘continent’ column, whereas it works for the entire set of columns.

    February 15, 2012 at 11:35 pm
  • Shlomi Noach Reply

    Sorry, link for first example went missing. Here it is:
    correct query with unfair warning

    February 15, 2012 at 11:36 pm
  • Shlomi Noach Reply

    Another input:
    Comparison of column = NULL is a common error. It should be reported.

    February 16, 2012 at 12:10 am
  • Henk Jan Agteresch Reply

    Realy appreciate the tools on tools.percona.com.

    Please make the signup mail plain text instead of html only

    February 16, 2012 at 3:25 am
  • Baron Schwartz Reply

    Robert, glad you like it — it’s meant to work best with MySQL, but a lot of the advice is DB-agnostic, so although I hadn’t thought about it, it should work in a lot of cases with any database. I’d report the bug you found, but I think I can predict the outcome: http://bugs.mysql.com/bug.php?id=44833

    Shlomi, thanks for the bug reports!

    Henk, I’ll check into that, thanks for the suggestion!

    February 16, 2012 at 6:57 am
  • Baron Schwartz Reply

    By the way, I didn’t really say this in explicitly, but the reason I personally wanted to build this tool is for the hard queries, not the easy ones. I’m thinking about times that someone sends me a massive query with a 20-table join and 50 WHERE clauses, and I want to know whether any of the WHERE clauses converts a LEFT to INNER join. That kind of thing. Reading SQL can be hard work, and I’m lazy 🙂

    February 16, 2012 at 7:03 am
  • xerxes Reply

    Excellent work guys! It’s amazing you make tools like this public (not to mention the Toolkit) – I bet Oracle would charge $$.

    Is it possible for you guys to make the signups based on any OpenID-like service? It would streamline usage heavily if I connect from several locations but didn’t grab my login details on the way out.

    February 17, 2012 at 3:58 am
  • Baron Schwartz Reply

    We’ve had a few requests for OpenID logins, and I will put it on our roadmap to investigate. Thanks!

    February 17, 2012 at 6:41 am
  • bob Reply

    On the subject of OpenID logins: why login at all?

    What the hell is wrong with this captcha system? And why does it keep reselecting “notify me”?

    February 21, 2012 at 8:02 am
  • harish Reply

    please help this query

    SELECT rep_name FROM representative WHERE rep_id IN (SELECT rep_id FROM rep_area WHERE PIN_ID IN (SELECT pin_id FROM pincode_list WHERE pincode IN (SELECT PINCODE FROM areawise_temp WHERE pro_name = ‘PRODUCT 125’ AND (bill_date BETWEEN ‘2015/02/01’ AND ‘2015/02/04’) AND our_cust_id “” AND our_product_id “” GROUP BY pincode) GROUP BY PIN_ID) GROUP BY rep_id)

    May 15, 2015 at 9:51 am

Leave a Reply