Module: QueryReviewer::MysqlAnalyzer

Included in:
SqlSubQuery
Defined in:
lib/query_reviewer/mysql_analyzer.rb

Instance Method Summary collapse

Instance Method Details

#analyze_extras! ⇒ Object 



42
43
44
45
46
47
48
49
50
51
52
53
54
 
# File 'lib/query_reviewer/mysql_analyzer.rb', line 42

def analyze_extras!
  if self.extra.match(/range checked for each record/)
    warn :severity => 4, :problem => "Range checked for each record", :desc => "MySQL found no good index to use, but found that some of indexes might be used after column values from preceding tables are known"
  end

  if self.extra.match(/using filesort/)
    warn :severity => 2, :problem => "Using filesort", :desc => "MySQL must do an extra pass to find out how to retrieve the rows in sorted order."
  end

  if self.extra.match(/using temporary/)
    warn :severity => 10, :problem => "Using temporary table", :desc => "To resolve the query, MySQL needs to create a temporary table to hold the result."
  end
end

#analyze_key! ⇒ Object 



34
35
36
37
38
39
40
 
# File 'lib/query_reviewer/mysql_analyzer.rb', line 34

def analyze_key!
  if self.key == "const"
    praise "Way to go!"
  elsif self.key.blank? && !self.extra.include?("select tables optimized away") && !self.extra.include?("impossible where noticed after reading const tables")
    warn :severity => 6, :field => "key", :desc => "No index was used here. In this case, that meant scanning #{self.rows} rows."
  end
end

#analyze_keylen! ⇒ Object 



56
57
58
59
60
 
# File 'lib/query_reviewer/mysql_analyzer.rb', line 56

def analyze_keylen!
  if self.key_len && !self.key_len.to_i.nil? && (self.key_len.to_i > QueryReviewer::CONFIGURATION["max_safe_key_length"])
    warn :severity => 4, :problem => "Long key length (#{self.key_len.to_i})", :desc => "The key used for the index was rather long, potentially affecting indices in memory"
  end
end

#analyze_query_type! ⇒ Object 



19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
# File 'lib/query_reviewer/mysql_analyzer.rb', line 19

def analyze_query_type!
  case query_type
  when "system", "const", "eq_ref" then
    praise("Yay")
  when "ref", "ref_or_null", "range", "index_merge" then
    praise("Not bad eh...")
  when "unique_subquery", "index_subquery" then
    #NOT SURE
  when "index" then
    warn(:severity => 8, :field => "query_type", :desc => "Full index tree scan (slightly faster than a full table scan)") unless !extra.include?("using where")
  when "all" then
    warn(:severity => 9, :field => "query_type", :desc => "Full table scan") unless !extra.include?("using where")
  end
end

#analyze_select_type! ⇒ Object 



11
12
13
14
15
16
17
 
# File 'lib/query_reviewer/mysql_analyzer.rb', line 11

def analyze_select_type!
  if select_type.match /uncacheable subquery/
    warn(:severity => 10, :field => "select_type", :desc => "Subquery must be run once for EVERY row in main query")
  elsif select_type.match /dependent/
    warn(:severity => 2, :field => "select_type", :desc => "Dependent subqueries can not be executed while the main query is running")
  end
end

#do_mysql_analysis! ⇒ Object 



3
4
5
6
7
8
9
 
# File 'lib/query_reviewer/mysql_analyzer.rb', line 3

def do_mysql_analysis!
  analyze_select_type!
  analyze_query_type!
  analyze_key!
  analyze_extras!
  analyze_keylen!
end