20100928000952_aggressive_indexing_strategy2.rb
1.34 KB
1
2
3
4
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
31
32
33
34
35
36
37
38
39
40
41
42
class AggressiveIndexingStrategy2 < ActiveRecord::Migration
def self.up
add_index(:action_tracker_notifications, :profile_id)
add_index(:action_tracker_notifications, :action_tracker_id)
say 'Removing duplicate notification records ...'
buffer = ''
removed = 0
select_all(
'select min(id) as min_id, action_tracker_id, profile_id, count(*)
from action_tracker_notifications
group by action_tracker_id, profile_id
having count(*) > 1'
).each do |duplicate|
buffer += ('delete from action_tracker_notifications
where
profile_id = %d AND
action_tracker_id = %s AND
id > %d;
' % [duplicate['profile_id'], duplicate['action_tracker_id'], duplicate['min_id']]
)
if removed % 100 == 0
execute buffer
say "Deleted " + removed.to_s
buffer = ''
end
removed += 1
end
if !buffer.empty?
execute buffer
end
add_index(:action_tracker_notifications, [:profile_id, :action_tracker_id], :unique => true, :name => "index_action_tracker_notif_on_prof_id_act_tracker_id")
end
def self.down
remove_index(:action_tracker_notifications, :profile_id)
remove_index(:action_tracker_notifications, :action_tracker_id)
remove_index(:action_tracker_notifications, [:profile_id, :action_tracker_id])
end
end