I’m brand new to SQL today and I'm designing a sole table which will be used to load likes of a post, in order, n at a time. Ex: Load the first 10 likes for a post, later load the next 10, etc.
I'm curious if this table design and query will be optimal? (All other data is in a NoSQL database ~ total count of likes is not needed).
To be more specific; will ORDER BY likeID and WHERE likeID > (starting point) slow down the query or use unnecessary resources? (the likeID will auto-increment, but some likes may be deleted/removed from the table at some point. There may be millions of likes recorded in this table).
The postLikes table:
postID: string
userID: string
username: string
timestamp: int
likeID: uniqueID (int) - increments every like
A user loads the first 2 likes for a post:
SELECT username, userID, likeID
FROM postLikes
WHERE (postID = “a1b767eae” AND likeID > 0)
ORDER BY likeID ASC
LIMIT 2
returns:
[
{username: "user6", userID: "SHi29s29", likeID: 324},
{username: "user33", userID: "bsSU4s83", likeID: 1089}
]
Then the user loads the next two likes for the same post:
...
WHERE (postID = “a1b767eae” AND likeID > 1089)
ORDER BY likeID ASC LIMIT 2
returns:
[
{username: "user8", userID: "Bsh292he", likeID: 2934},
{username: "user543", userID: "sjXks28S", likeID: 10354}
]
CodePudding user response：
The pivotal ingredient for performance will be a matching multicolumn index:
CREATE INDEX ON post_likes (post_id, like_id);
With index columns in this order. See:
- Multicolumn index and performance
- SQL syntax term for 'WHERE (col1, col2) < (val1, val2)'
- Optimize query with OFFSET on large table
If the only other column in the SELECT list will be username, consider a covering index (requires Postgres 11 or later) like:
CREATE INDEX ON post_likes (post_id, like_id) INCLUDE (username);
And keep your table vacuumed to allow index-only scans. See:
- How does PostgreSQL perform ORDER BY with a b-tree index on the field?
- Postgres not using index when index scan is much better option
Oh, and don't use CaMeL-case identifiers in Postgres. See: