From: Date: January 3 2008 7:00am
Subject: what should an assert_efficient_sql check for?
List-Archive: http://lists.mysql.com/internals/35250
Message-Id: <477C79F6.1020503@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

MySQL internalists:

I want to write an assertion (for ActiveRecord, but that's not important here) 
which traps production SQL SELECT statements, calls EXPLAIN on each one, then 
parses their results, looking for tell-tail issues. (The goal is we wrap target 
code in assert_efficient_sql, then if a future upgrade makes the code 
inefficient, the assertion catches the problem before the clients do.)

Here's an example of my query failing:

   def test_assert_inefficient_sql
     assert_raise_message Test::Unit::AssertionFailedError,
                         /Pessimistic.*Accessory Load.*ALL/m do
       assert_efficient_sql do
         Accessory.find_by_sql('select * from accessories, rings
                                 where accessories.id = rings.id')
                              #  noooo! they don't link like that!
       end
     end
   end

That assertion would print this diagnostic (edited for width):

pessimistic query Accessory Load!
select * from accessories, rings where accessories.id = rings.id
  select_type | key_len | type   | rows | table       | ref             | key
--------------------------------------------------------------------------------
  SIMPLE      |         | ALL    | 1    | rings       |                 |
  SIMPLE      | 4       | eq_ref | 1    | accessories | testdb.rings.id | PRIMARY

The assertion failed, semantically, because this application has no good reason 
to JOIN accessories and rings, so they have no index in common.

Syntactically, it fails because it has a type of ALL. (I heard somewhere that 
reading every row in a table, just to find one record, is bad DB Karma.)

The relevant source code, inside the assertion, just goes 'ALL' == explanation 
['type'].

The question now is what else can I load up inside the assertion? What other 
checks should

   A> always,
   B> frequently, or
   C> opportunistically

pass in the response from EXPLAIN, to detect unneeded DB loading?

(Please rank suggestions as listed, because a multi-tiered assert_efficient_sql 
is more valuable than one that only targets A, such as "ALL"!)

This assertion is for _unit_ tests, not customer acceptance tests or QA tests, 
so I hope to bypass the time-honored method of loading up a billion records, 
running them for 20 minutes, and profiling them. Developers should learn within 
seconds if their new SQL statements are inefficient, not within days, or longer.

-- 
   Phlip
   http://www.oreillynet.com/onlamp/blog/2007/07/assert_latest_and_greatest.html