assert_efficient_sql
Date : 01 13 2008 Category : Web
O'Reilly Network Articles and WeblogsThe assertion intercepts and copies out your MySQL SELECT statements, then calls EXPLAIN on each one, and inspects the results for common problems.
This article is a reference for this assertion’s options. The techniques should be ported to any database with an EXPLAIN
Credits to Jay Pipe for his excellent little survey of ways to detect pessimizations!
Installpiston is the best way to install this assertion:
piston import svn://rubyforge.org/var/svn/efficient-sql/ vendor/plugins/efficient-sqlThe documented Rails way is the worst:
script/plugin install -x svn://rubyforge.org/var/svn/efficient-sqlThe -x option bonds your Subversion repository to rubyforge.org. Each time you svn update your own code, you will also automatically get the latest version of the plugin, whether you need it or not. piston avoids these fun issues. You can update on command, with piston update…
DiagnoseIf your SQL is already efficient, use :verbose to diagnose why it‘s efficient:
def test_verbose assert_stdout /select_type/ do assert_efficient_sql :verbose do Foo.find_by_id(42) end end endIt prints a table like this to STDOUT:
query for SELECT * FROM foos WHERE (foos.`id` = 42) LIMIT 1 Foo Load select_type | key_len | type | id | Extra | possible_keys | table | rows | ref | key ------------------------------------------------------------------------------------------- SIMPLE | 4 | const | 1 | | PRIMARY | foos | 1 | const | PRIMARYReading a table‘s primary key is naturally efficient, so the type field is a healthy const.
See the MySQL document Optimizing Queries with EXPLAIN to learn what those results mean.
OptimizeThis test shows assert_efficient_sql failing: def test_assert_inefficient_sql assert_flunked /Pessimistic.* full.table.scan.* Foo.Load/mx do assert_efficient_sql do Foo.find_by_sql('select * from foos a') end end end
Its failure would look like this:
1) Failure: test_assert_inefficient_sql(AssertEfficientSqlTest) [./lib/assert_efficient_sql.rb:220:in `analyze_efficiency' ./test/assert_efficient_sql_test.rb:67:in `test_assert_inefficient_sql']: Pessimistic query for select * from foos a full table scan Foo Load select_type | key_len | type | id | Extra | possible_keys | table | rows | ref | key ------------------------------------------------------------------------------------ SIMPLE | | ALL | 1 | | | a | 43 | | . 7 tests, 9 assertions, 1 failures, 0 errorsMost queries should not blindly read ALL rows in a database table.
If that query used a WHERE condition that selects fewer records, but if EXPLAIN produced a query type of ALL, this would indicate MySQL might read the entire database table just to apply the WHERE. The best fix, in that situation, is to add an index (a MySQL "KEY") to the table, and put the fields from that WHERE condition into it.
That technique allows MySQL to read the index first, then chop directly thru the table to the records you need.
PessimizeSometimes you need an ALL, even while other assert_efficient_sql checks must pass. To positively declare we like ALL, pass in :ALL => true.
def test_assert_all assert_efficient_sql :ALL => true do Foo.find(:all) end endAssertions use positive reinforcement; they state conditions we like. They don‘t try to deny conditions we don‘t like. If :ALL => false meant "false to warn about ALL", the assertion would confuse developers. So we use :ALL => true to allow queries to scan all rows.
OptionsIf assert_efficient_sql (generally) dislikes your arguments, it will print out its default options, each with an explanation.
def test_help assert_stdout /invalid.*argument.* verbose.*=>.*false/mx do assert_efficient_sql(:help){} end end NestingYou can also nest the assertion, to provide different options for different blocks. The assertion allows this because your test might also have some other reason to use blocks.
def test_nest outer_result = assert_efficient_sql do inner_result = assert_efficient_sql :ALL => true do Foo.find(:all) end assert_no_match /where/i, inner_result[0][0] Foo.find(42) end assert_match /where/i, outer_result[0][0] end Using filesortIf your WHERE and ORDER clauses are too complex, MySQL might need to write a file (or worse), just to satisfy a query. assert_efficient_sql detects this pernicious situation:
def test_prevent_filesorts _exec %[ CREATE TABLE `t1` ( `a` int(11) NOT NULL DEFAULT '0', `b` blob NOT NULL, `c` text NOT NULL, PRIMARY KEY (`a`,`b`(255),`c`(255)), KEY `t1ba` (`b`(10),`a`) ) ENGINE=InnoDB ] assert_flunked /Using.filesort/ do assert_efficient_sql do Foo.find_by_sql('SELECT a FROM t1 ORDER BY b') end end ensure _exec 'drop table t1' endIf you want to use the assertion without faulting on filesorts, pass the option :Using_filesort => true.
ThrottleOne common pessimization is a query that reads thousands of rows just to return a few. assert_efficient_sql counts the rows hit in each phase of an SQL SELECT, and faults if any row count exceeds 1,000.
Adjust this count with :throttle => 42.
def test_throttle 101.times{|x| Foo.create :name => "foo_#{ x }" } assert_flunked /Pessimistic.* more.than.*100.* Foo.Load/mx do assert_efficient_sql :throttle => 100, :ALL => true do Foo.find(:all) end end end SHOW SESSION STATUSassert_efficient_sql calls SHOW SESSION STATUS before and after its sampled block. If you are seeking an advanced pessimization, such as Created_tmp_disk_tables, pass :Created_tmp_disk_tables => 0. The assertion will compare difference in STATUS before and after calling its block. A difference greater than the allowed difference will trigger a fault.
To test this, we simply detect a STATUS variable which is not a warning.
def test_declare_futile_war_on_Innodb_rows_read assert_flunked /just.for.test.* Innodb_rows_read/mx do assert_efficient_sql :diagnostic => 'just for test!', :Innodb_rows_read => 0 do Foo.find(:all) end end end Inconclusionassert_inefficient_sql is available at http://efficient-sql.rubyforge.org/. Watch that space (and this) for more tuning and pessimization detections.