PERF: index on topic_id for user_histories table

PERF: index on topic_id for user_histories table

We query this table when getting composer messages with the queries such as:

SELECT  1 AS one FROM "user_histories"
WHERE "user_histories"."target_user_id" = 1 AND
 "user_histories"."action" = 9 AND
 "user_histories"."topic_id" = 105794
LIMIT 1

This index ensures this query remains very quick, regardless of user history size.

diff --git a/db/migrate/20190106041015_add_topic_id_index_to_user_histories.rb b/db/migrate/20190106041015_add_topic_id_index_to_user_histories.rb
new file mode 100644
index 0000000..8172657
--- /dev/null
+++ b/db/migrate/20190106041015_add_topic_id_index_to_user_histories.rb
@@ -0,0 +1,5 @@
+class AddTopicIdIndexToUserHistories < ActiveRecord::Migration[5.2]
+  def change
+    add_index :user_histories, [:topic_id, :target_user_id, :action]
+  end
+end

GitHub sha: 37088c42

1 Like