migrations/2023-07-14-154840_add_optimized_indexes_published/up.sql

   1 -- Drop the old indexes
   2 DROP INDEX idx_post_aggregates_featured_local_hot;
   3
   4 DROP INDEX idx_post_aggregates_featured_local_active;
   5
   6 DROP INDEX idx_post_aggregates_featured_local_score;
   7
   8 DROP INDEX idx_post_aggregates_featured_community_hot;
   9
  10 DROP INDEX idx_post_aggregates_featured_community_active;
  11
  12 DROP INDEX idx_post_aggregates_featured_community_score;
  13
  14 DROP INDEX idx_comment_aggregates_hot;
  15
  16 DROP INDEX idx_comment_aggregates_score;
  17
  18 -- Add a published desc, to the end of the hot and active ranks
  19 -- Add missing most comments index
  20 CREATE INDEX idx_post_aggregates_featured_local_most_comments ON post_aggregates (featured_local DESC, comments DESC, published DESC);
  21
  22 CREATE INDEX idx_post_aggregates_featured_community_most_comments ON post_aggregates (featured_community DESC, comments DESC, published DESC);
  23
  24 -- featured_local
  25 CREATE INDEX idx_post_aggregates_featured_local_hot ON post_aggregates (featured_local DESC, hot_rank DESC, published DESC);
  26
  27 CREATE INDEX idx_post_aggregates_featured_local_active ON post_aggregates (featured_local DESC, hot_rank_active DESC, published DESC);
  28
  29 CREATE INDEX idx_post_aggregates_featured_local_score ON post_aggregates (featured_local DESC, score DESC, published DESC);
  30
  31 -- featured_community
  32 CREATE INDEX idx_post_aggregates_featured_community_hot ON post_aggregates (featured_community DESC, hot_rank DESC, published DESC);
  33
  34 CREATE INDEX idx_post_aggregates_featured_community_active ON post_aggregates (featured_community DESC, hot_rank_active DESC, published DESC);
  35
  36 CREATE INDEX idx_post_aggregates_featured_community_score ON post_aggregates (featured_community DESC, score DESC, published DESC);
  37
  38 -- Fixing some comment aggregates ones
  39 CREATE INDEX idx_comment_aggregates_hot ON comment_aggregates (hot_rank DESC, published DESC);
  40
  41 CREATE INDEX idx_comment_aggregates_score ON comment_aggregates (score DESC, published DESC);
  42