I’ve been creating an online platform that allows users to follow other users and tags. The table where you can follow other users is called the Relationships table.
schema "relationships" do
belongs_to :follower, Writer, type: :binary_id
belongs_to :following, Writer, type: :binary_id
timestamps()
end
The table where you can follow other tags is called the interested_in_topics table.
schema "interested_in_topics" do
belongs_to :writer, Writer, type: :binary_id
belongs_to :topics, Topics, type: :binary_id
end
All articles can be placed under up to five tags and this information is stored in the categories_articles table
schema "categories_articles" do
belongs_to :categories, Topics, type: :binary_id
belongs_to :articles, Articles, type: :binary_id
end
When I want the user feed, I get all the articles that each writer the user has followed has written and all the articles tagged to the tags the user is following, then return them on distinct. The query takes about 15 seconds, is there anyway i can speed this up??? I’ve already indexed my foreign keys in the many to many tables above.
def list_feed_articles(writer_id, params \\ %{}) do
query =
from(articles in Articles,
preload: [:categories, :writer],
left_join: r in Relationships,
on: r.follower_id == ^writer_id,
left_join: t in InterestedinTopics,
on: t.writer_id == ^writer_id,
left_join: ac in CategoriesArticles,
on: t.topics_id == ac.categories_id,
where: articles.writer_id == r.following_id or
articles.writer_id == ^writer_id,
or_where: ac.articles_id == articles.id,
order_by: [desc: articles.inserted_at],
select: [:id, :title, :description, :cover_photo, :writer_id, categories: [:id, :slug, :title], writer: [:id, :username, :name, :avatar]],
distinct: [articles.id]
)
Repo.paginate(query, params)
end
, I would suggest you to add your data columns to the select explicitly, even try removing preload and select the required columns. Do ping your latest sql query.
