from here on out, ill post codecel questions or other shit i need help with here and give mbux rewards appropriately
subscribe to the thread if ur interested
from here on out, ill post codecel questions or other shit i need help with here and give mbux rewards appropriately
subscribe to the thread if ur interested
Jump in the discussion.
No email address required.
You should explain exactly what you're trying to achieve with the query. There may be better approaches.
Jump in the discussion.
No email address required.
"hot" sorting algorithm
Jump in the discussion.
No email address required.
Consider doing an initial filter to cull out posts older than a week before trying to do the math on their scores? Basically toss out entries that don't have a snowball's chance of being selected.
Jump in the discussion.
No email address required.
how do i do that king
Jump in the discussion.
No email address required.
Depends on which SQL engine you're using, but as
@cyberdick mentioned doing a nested query is one way. Otherwise your engine might be smart enough to figure this out if you just add an additional
WHERE
clause specifyingAND date > x
.From your analysis it looks like most of the time is being spent calculating the sort key for every post ever made, then sorting that. Figure out at which age the other factors in the stat become irrelevant (e.g. after 2 weeks no amount of activity is getting the post a good score), you only need to care about exact scoring for posts that might tangibly be considered "hot".
Jump in the discussion.
No email address required.
More options
Context
select * from posts where posts.date > {date} ??? and then run your query this? or create a view with this and use the view
Jump in the discussion.
No email address required.
Also add some stricter thresholds for what counts as "hot"! The amount of times I've seen a post with only Snappy commenting and 3 upmarseys near the top of the hot sorting makes no sense
Jump in the discussion.
No email address required.
More options
Context
More options
Context
More options
Context
More options
Context
More options
Context
More options
Context
Don't do this for one.
Jump in the discussion.
No email address required.
y
Jump in the discussion.
No email address required.
Honestly depends on your database but for most of the ones I know and have worked with NULL queries are outright useless at best in terms of selectivity and depending on how a search is done might be harmful at worst.
Jump in the discussion.
No email address required.
More options
Context
More options
Context
More options
Context
Probably not with adding a simple index.
I would add a new column for "rank", index that new column sorted ASC. Then have a job that runs every X minutes that computes and updates each post rank.
When querying, you can sort/limit by the pre-computed rank but then apply a live rank algo in real-time so that it's not stale from the job but would need some tweaks/optimizations to work around page boundaries accurately.
Jump in the discussion.
No email address required.
More options
Context