Class: MysqlIndex

Inherits:
Object
  • Object
show all
Includes:
DataMapper::Resource
Defined in:
lib/dm-cutie-extras/hooks/mysql_index.rb

Overview

This isn’t a Tracker::Hook, its actually and After Create hook for RepositoryStorage and ExecutedQuery

In calculating the selectivity, since the number of records can change, I do the average selectivity

and the final selectivity (on stopping Dm Cutie)

Constant Summary collapse

STATEMENT = 
"SHOW INDEX FROM %s"

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.execute(target_repo, storage_name) ⇒ Object

CLASS METHODS #########



76
77
78
 
# File 'lib/dm-cutie-extras/hooks/mysql_index.rb', line 76

def self.execute( target_repo, storage_name )
  repository(target_repo.to_sym).adapter.query MysqlIndex::STATEMENT % storage_name
end

.process_executed_query(executed_query) ⇒ Object

Track the selectivity of an index



81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
 
# File 'lib/dm-cutie-extras/hooks/mysql_index.rb', line 81

def self.process_executed_query( executed_query )
  executing_repo = executed_query.generalized_query.primary_storage
  
  MysqlIndex.all(MysqlIndex.repository_storage.id => executing_repo.id).each do |mysql_index|
    # Get to the repo BEING tracked for distinct count      
    distinct_column_count = DataMapper.repository(executing_repo.repo_name.to_sym) do |exec_repo|
      exec_repo.adapter.query(
        "SELECT COUNT(DISTINCT(`#{mysql_index.column_name}`)) FROM `#{mysql_index.table}`"
      ).first
    end
    
    DataMapper::Transaction.new.link do
      mysql_index.distinct_count = distinct_column_count
      mysql_index.execution_count += 1
      mysql_index.save
    end
  end
end

.process_repository_storage(repository_storage) ⇒ Object

Track the indexes on a Repository Storage



103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
# File 'lib/dm-cutie-extras/hooks/mysql_index.rb', line 103

def self.process_repository_storage( repository_storage )
  # Perform the SHOW index on the repos storage
  raw_index_list = MysqlIndex.execute( repository_storage.repo_name, repository_storage.storage_name )

  #Store the MysqlIndex in the DM Cutie repo
  DataMapper::Cutie.repo do
    raw_index_list.each_with_index do |shown_index, idx| 
      attribs = shown_index.attributes
      attribs.delete(:comment)
      attribs[:sequence_in_index]      = attribs.delete(:seq_in_index)
      mysql_index = MysqlIndex.new(attribs)
      mysql_index.repository_storage = repository_storage

      mysql_index.save
    end
  end
end

Instance Method Details

#duplicate ⇒ Object

detect duplicate indexes



66
67
68
69
70
71
72
73
 
# File 'lib/dm-cutie-extras/hooks/mysql_index.rb', line 66

def duplicate
  MysqlIndex.all(
    :id.not       => self.id,
    :table        => self.table,
    :index_type   => self.index_type,
    :column_name  => self.column_name
  ).count != 0
end

#selectivity ⇒ Object

methods for selectivity

selectivity is calculated by count(*) / count(distinct(COLUMN_NAME)) the closer to 1.0 
the better the index



36
37
38
 
# File 'lib/dm-cutie-extras/hooks/mysql_index.rb', line 36

def selectivity
  ( distinct_count.to_f / self.repository_storage.record_count.to_f )
end

#table_read_to_write_ratio ⇒ Object

Shortcut method to R:W of repo



41
42
43
 
# File 'lib/dm-cutie-extras/hooks/mysql_index.rb', line 41

def table_read_to_write_ratio
  self.repository_storage.read_to_write_ratio
end

#usefulness ⇒ Object

TODO Row length could be a useful addition to calculating this TODO Potential Uses vs Actual Uses could be a useful addition to calculating this



48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
 
# File 'lib/dm-cutie-extras/hooks/mysql_index.rb', line 48

def usefulness
  # Number of records, read to write ratio, selectivity
  num_records = self.repository_storage.record_count
  num_reads   = self.repository_storage.total_selects
  num_writes  = self.repository_storage.total_inserts + 
                  self.repository_storage.total_deletes + 
                  self.repository_storage.total_updates 

  records_processed = num_records * execution_count
  total_operations  = (num_reads + num_writes).to_f
  read_percentage   = (num_reads / total_operations)
  # write_percentage  = (num_writes / total_operations)
  return( 
    (total_operations * read_percentage * selectivity) / total_operations
  )
end