Argh. So I was working with my database this morning, and I accidentally did an
update comments set date='blah'; and very notably left off the
where cid=d clause. So it said it updated thousands of records, I cursed, and I immediately typed
rollback; to which mysql replied:
ERROR 1196: Warning: Some non-transactional changed tables couldn't be rolled back
I cursed again, and reminded myself “No problem, that’s why I keep nightly database backups.”
Only when I looked, it turns out I was never backing up the comments table. So now I’m in limbo waiting for dreamhost to reply and let me know if they actually make backups and can give me a copy of the table… If they can’t, I’m going to have to have to get very personal with the google cache of Linkstew…
So, for now, this means that if you’re looking at old entries, all of the dates for the comments aren’t just going to be wrong, they’re all going to be the same. Blah. Talk about a bad way to start a day.
update: Okay, they restored the table from backup for me, even though I asked them to give me a copy of the dump so I could restore it myself… Unfortunately, this means if you left a comment between 2003-04-28 22:52:18 and 2003-04-30 16:00ish, it was lost. That was at least one comment that I know of… Blah. Oh well. It’s not like I’m that popular or anything. =)
I finally got around to fixing Linkstew’s slowness.
So what was wrong?
I started by putting in timing code to see how long each piece of the page was taking to render, and I found that all of the hang up was in building the central list of posts. Further poking revealed that it was the sql query itself that was taking anywhere from 4 to 10 seconds to execute (depending on mysql’s mood), and this was the source of the long loading delays the site had been exhibiting.
Now, I hadn’t changed that query in months, so I still can’t account for why Linktew suddenly got so slow last month. But using the “
explain” SQL command, I was pretty quickly able to deduce the source of my problem:
My comments table has a column that keeps track of the post number that any given comment is associated with. However, the entry number column (which I was joining my posts table to my comments table on) wasn’t indexed. So to display the comment count for any given entry, my query had to do a full, unindexed scan of my comments table in order to find out how many comments any given entry had. Multiply this by every entry on Linkstew, and I had one expensive operation on my hand.
A simple little
alter table comments add index ... fixed the problem good, and the query that was taking 4 to 10 seconds now takes 0.15 to 0.30 seconds.
Which means that Linkstew is once again faster than Fury.com, and all is again right in the world. ;-)
(I’m just trying to make Kevin’s life difficult. He knows I love him.)