PERF: Add index for (action_type,created_at)` on `user_actions` table.

PERF: Add index for (action_type,created_at)onuser_actions` table.

Speeds up counting of likes in About#stats.

diff --git a/db/migrate/20190405044140_add_index_action_type_created_at_on_user_actions.rb b/db/migrate/20190405044140_add_index_action_type_created_at_on_user_actions.rb
new file mode 100644
index 0000000..b0d8be1
--- /dev/null
+++ b/db/migrate/20190405044140_add_index_action_type_created_at_on_user_actions.rb
@@ -0,0 +1,5 @@
+class AddIndexActionTypeCreatedAtOnUserActions < ActiveRecord::Migration[5.2]
+  def change
+    add_index :user_actions, [:action_type, :created_at]
+  end

GitHub sha: ca4c9abe

1 Like

What is the actual query this optimizes are we counting all action_types in the site?

We’re counting likes only but I can easily see this being used for other action types.

What is the SQL for the query?

Select count(*) from user_actions where action_type = ? and created_at > ?

This is so strange, you would think we join into the topics/posts otherwise we count deleted content here and private content?

1 Like

I think it is meant to include those.

Now that you mentioned it… the counts are quite messed up. Topics count only includes listable topics but posts count includes all posts.


hmmm I guess I will accept this new index for now, may make to follow this up at some point in the medium / far future.

1 Like