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
+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.

2 Likes

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