Module: SqlSafetyNet::ExplainPlan::Mysql

Defined in:
lib/sql_safety_net/explain_plan/mysql.rb

Overview

Include this module in your MySQL connection class to analyze the query plan generated by MySQL.

Instance Method Summary collapse

Instance Method Details

#sql_safety_net_analyze_query_plan(sql, binds) ⇒ Object 



5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
# File 'lib/sql_safety_net/explain_plan/mysql.rb', line 5

def sql_safety_net_analyze_query_plan(sql, binds)
  alerts = []
  config = SqlSafetyNet.config
  explain_results = select("EXPLAIN #{sql}", "EXPLAIN", binds)
  
  explain_results.each do |row|
    select_type = (row['select_type'] || '').downcase
    type = (row['type'] || '').downcase
    rows = row['rows'].to_i
    extra = (row['Extra'] || '').downcase
    key = row['key']
    possible_keys = row['possible_keys']

    alerts << "table scan on #{rows} rows" if (type.include?('all') && rows > config.table_scan_limit)
    alerts << "no index used" if (key.blank? && rows > config.table_scan_limit)
    alerts << "no index possible" if (possible_keys.blank? && rows > config.table_scan_limit)
    alerts << "dependent subquery" if select_type.include?('dependent')
    alerts << "uncacheable subquery" if select_type.include?('uncacheable')
    alerts << "full scan on null key" if extra.include?('full scan on null key')
    alerts << "uses temporary table for #{rows} rows" if extra.include?('using temporary') && rows > config.temporary_table_limit
    alerts << "uses filesort for #{rows} rows" if extra.include?('filesort') && rows > config.filesort_limit
    alerts << "examined #{rows} rows" if rows > config.examined_rows_limit
  end
  
  alerts
end