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
