If anyone bothered to actually look at the SQL SELECT that Lemmy uses to list posts every time you hit refresh it would be blindingly obvious how convoluted it is. yet the community does not talk about the programming issues and instead keeps raising money for 64 core hardware upgrades without recognizing just how tiny Lemmy’s database really is and how 57K users is not a large number at all!
your original one, friend. I wouldn’t have argued this point if you had started here.
I mentioned “ORM” right in my first comment.
<span style="color:#323232;">SELECT
</span><span style="color:#323232;"> "post"."id" AS post_id, "post"."name" AS post_title,
</span><span style="color:#323232;"> -- "post"."url", "post"."body", "post"."creator_id", "post"."community_id", "post"."removed", "post"."locked", "post"."published", "post"."updated", "post"."deleted", "post"."nsfw", "post"."embed_title", "post"."embed_description", "post"."thumbnail_url",
</span><span style="color:#323232;"> -- "post"."ap_id", "post"."local", "post"."embed_video_url", "post"."language_id", "post"."featured_community", "post"."featured_local",
</span><span style="color:#323232;"> "person"."id" AS p_id, "person"."name",
</span><span style="color:#323232;"> -- "person"."display_name", "person"."avatar", "person"."banned", "person"."published", "person"."updated",
</span><span style="color:#323232;"> -- "person"."actor_id", "person"."bio", "person"."local", "person"."private_key", "person"."public_key", "person"."last_refreshed_at", "person"."banner", "person"."deleted", "person"."inbox_url", "person"."shared_inbox_url", "person"."matrix_user_id", "person"."admin",
</span><span style="color:#323232;"> -- "person"."bot_account", "person"."ban_expires",
</span><span style="color:#323232;"> "person"."instance_id" AS p_inst,
</span><span style="color:#323232;"> "community"."id" AS c_id, "community"."name" AS community_name,
</span><span style="color:#323232;"> -- "community"."title", "community"."description", "community"."removed", "community"."published", "community"."updated", "community"."deleted",
</span><span style="color:#323232;"> -- "community"."nsfw", "community"."actor_id", "community"."local", "community"."private_key", "community"."public_key", "community"."last_refreshed_at", "community"."icon", "community"."banner",
</span><span style="color:#323232;"> -- "community"."followers_url", "community"."inbox_url", "community"."shared_inbox_url", "community"."hidden", "community"."posting_restricted_to_mods",
</span><span style="color:#323232;"> "community"."instance_id" AS c_inst,
</span><span style="color:#323232;"> -- "community"."moderators_url", "community"."featured_url",
</span><span style="color:#323232;"> ("community_person_ban"."id" IS NOT NULL) AS ban,
</span><span style="color:#323232;"> -- "post_aggregates"."id", "post_aggregates"."post_id", "post_aggregates"."comments", "post_aggregates"."score", "post_aggregates"."upvotes", "post_aggregates"."downvotes", "post_aggregates"."published",
</span><span style="color:#323232;"> -- "post_aggregates"."newest_comment_time_necro", "post_aggregates"."newest_comment_time", "post_aggregates"."featured_community", "post_aggregates"."featured_local",
</span><span style="color:#323232;"> --"post_aggregates"."hot_rank", "post_aggregates"."hot_rank_active", "post_aggregates"."community_id", "post_aggregates"."creator_id", "post_aggregates"."controversy_rank",
</span><span style="color:#323232;"> -- "community_follower"."pending",
</span><span style="color:#323232;"> ("post_saved"."id" IS NOT NULL) AS save,
</span><span style="color:#323232;"> ("post_read"."id" IS NOT NULL) AS read,
</span><span style="color:#323232;"> ("person_block"."id" IS NOT NULL) as block,
</span><span style="color:#323232;"> "post_like"."score",
</span><span style="color:#323232;"> coalesce(("post_aggregates"."comments" - "person_post_aggregates"."read_comments"), "post_aggregates"."comments") AS unread
</span><span style="color:#323232;">
</span><span style="color:#323232;">FROM (
</span><span style="color:#323232;"> ((((((((((
</span><span style="color:#323232;"> (
</span><span style="color:#323232;"> (
</span><span style="color:#323232;"> "post_aggregates"
</span><span style="color:#323232;"> INNER JOIN "person" ON ("post_aggregates"."creator_id" = "person"."id")
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> INNER JOIN "community" ON ("post_aggregates"."community_id" = "community"."id")
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "community_person_ban"
</span><span style="color:#323232;"> ON (("post_aggregates"."community_id" = "community_person_ban"."community_id") AND ("community_person_ban"."person_id" = "post_aggregates"."creator_id"))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> INNER JOIN "post" ON ("post_aggregates"."post_id" = "post"."id")
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "community_follower" ON (("post_aggregates"."community_id" = "community_follower"."community_id") AND ("community_follower"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "community_moderator" ON (("post"."community_id" = "community_moderator"."community_id") AND ("community_moderator"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "post_saved" ON (("post_aggregates"."post_id" = "post_saved"."post_id") AND ("post_saved"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "post_read" ON (("post_aggregates"."post_id" = "post_read"."post_id") AND ("post_read"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "person_block" ON (("post_aggregates"."creator_id" = "person_block"."target_id") AND ("person_block"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "post_like" ON (("post_aggregates"."post_id" = "post_like"."post_id") AND ("post_like"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "person_post_aggregates" ON (("post_aggregates"."post_id" = "person_post_aggregates"."post_id") AND ("person_post_aggregates"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "community_block" ON (("post_aggregates"."community_id" = "community_block"."community_id") AND ("community_block"."person_id" = 3)))
</span><span style="color:#323232;"> LEFT OUTER JOIN "local_user_language" ON (("post"."language_id" = "local_user_language"."language_id") AND ("local_user_language"."local_user_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;">WHERE (((((((
</span><span style="color:#323232;"> ((("community"."deleted" = false) AND ("post"."deleted" = false)) AND ("community"."removed" = false))
</span><span style="color:#323232;"> AND ("post"."removed" = false)) AND ("post_aggregates"."creator_id" = 3)) AND ("post"."nsfw" = false))
</span><span style="color:#323232;"> AND ("community"."nsfw" = false)) AND ("local_user_language"."language_id" IS NOT NULL))
</span><span style="color:#323232;"> AND ("community_block"."person_id" IS NULL))
</span><span style="color:#323232;"> AND ("person_block"."person_id" IS NULL))
</span><span style="color:#323232;">ORDER BY "post_aggregates"."featured_local" DESC , "post_aggregates"."published" DESC
</span><span style="color:#323232;">LIMIT 10
</span><span style="color:#323232;">OFFSET 0
</span><span style="color:#323232;">;
</span>