My Comment Page Stalling During Load

June 14, 2006 8:14 AM

Whenever I visit http://www.metafilter.com/index.cfm?sortby=mycommentedthreads the page load stalls at about 36kb for a good ten to twenty seconds. Every time. What's up with that?
posted by five fresh fish to Bugs at 8:14 AM (12 comments total)

It's an insanely deep query looking at 50k posts to see if any are yours. I should probably remove that option.
posted by mathowie (staff) at 8:28 AM on June 14, 2006

Why not make the query less insanely deep? Why not start with the comments, which are much less than 50k for nearly everyone before looking at the posts? I'm no database expert, but I don't think it should take ten seconds to do that kind of query.
posted by scottreynen at 8:55 AM on June 14, 2006

Right. Instead of searching every MeFi post ever for my comments and spitting out those, why not just search my comments and spit out the parent posts in reverse chronological order?
posted by Ryvar at 9:00 AM on June 14, 2006

Sorry, bad order of operation there: search my comments in reverse chronological order and THEN spit out the parent posts.
posted by Ryvar at 9:01 AM on June 14, 2006

Yeah, the query is messy but I haven't touched it in four years. Does anyone use it anymore? Why not just use the comment history in your profile?
posted by mathowie (staff) at 9:29 AM on June 14, 2006

Excellent idea.
posted by gsteff at 9:29 AM on June 14, 2006

I meant the optimization... although yeah, I never use the "sort by my comments" feature.
posted by gsteff at 9:31 AM on June 14, 2006

SELECT * FROM posts p, comments c WHERE p.post_id = c.post_id HAVING COUNT(fresh_fish) = 5
posted by Plutor at 12:14 PM on June 14, 2006

Why not just use the comment history in your profile?

Zigackly. I never touch the other feature.

Ooh. Someone knows SQL. But can you optimize queries as well as write them?
posted by scarabic at 4:23 PM on June 14, 2006

For one thing, you never "select *" in production or you're fired.
posted by scarabic at 4:23 PM on June 14, 2006

Let me give a shot at explaining how I'd expect it to be done:

a user id '13258' contains datum like 'five fresh fish', 'sekritpassword', a message id marking the global most-recent message viewed, a list of (thread id, message id marking m.r.m.v. in this thread) threads in which I've left a message, a message id marking the m.r.m.v. message of all those in that previous list, etc.

a thread id '[timestamp]' contains datum like a list of message ids, the thread title, the thread original post text and timestamp, a user id, the id of the most-recent message in this thread.

a message id '[timestamp]' contains datum like user_id='11856', 'For one thing, you never "select *" in production or you're fired.', and so on.

The function I'm using when I go to that page would

posted_threads_list = user[userId].talkyThreads
for (threadId, nvMsg) in posted_threads_list:
if thread[threadId].mrMsg > nvMsg: activeThreads.append(threadId)
else: inactiveThreads.append(threadId)
for threadId in activeThreads: displayThreadInfo(threadId)
for threadId in inactiveThreads: displayThreadInfo(threadId)

I use that URL because I want to see a list of threads, not messages. Sometimes the profile history doesn't show enough history. It always shows a nearly unreadable mash of comments, but usually not all the new comments. Etcetera. I don't like it at all.

buggery on the collapsed indentation. imagonna shorten the lines even further, then. bah.
posted by five fresh fish at 9:21 PM on June 14, 2006

scarabic: "For one thing, you never "select *" in production or you're fired."

I did that on purpose, because it's, you know, a slow query. Or so I'd heard. It was a riff on too-much-returned-data-ness. You didn't seem to notice my worse crime: a HAVING clause with no GROUP BY.

You also don't do dev on production, but, well, this is MeFi, not Luxembourg.
posted by Plutor at 5:14 AM on June 15, 2006