31st October, 2007
Site Modifications, NaBloPoMo
Wednesday, 9:20 am in sk.log
Some minor sk.log modifications.
I decided the other day that I didn’t have nearly enough ‘deep linking’ so in order to attempt to rectify that I’ve added ‘related’ links on each post page. For ages I wasn’t sure how to do this, until I bit the bullet and did a Google search and found out that – lo and behold – MySQL will pretty much do it for me using FULLTEXT. How useful!
So now we’ve got:
SELECT i.itemID, i.itemName, p.postLink
FROM `itemxref` i JOIN `log_posts` p
ON i.itemID = p.itemID
WHERE MATCH( i.itemText ) AGAINST ( '_______' )
AND i.itemModule = '/post'
-- ORDER BY RAND()
LIMIT 0, 3;
The ‘against’ criteria is actually the post’s tags, so naturally this will only show up on posts that (yanno) have tags; most of the old ones don’t, though I’m slowly trying to go through (all seven years of) the archives and add them. For a while I also tried ORDER BY RAND() mostly just because it’s fun, however it was interfering with MySQL’s relevance ranking on MATCH queries so that’s now gone.
Other small changes include:
- Some miscellaneous encoding stuff has been cleaned up. Because everyone loves neat code.
- As well as related links, posts now have a ‘plug list’ of commentors. I’m not really sure what the point of this is, but it seems popular so I decided it would be worth writing into the sk.log code.
- I’ve added a few microformats here and there, because I like that sort of thing.
- There is now an Atom 0.3 feed (finally). It will optionally take the same username and hash options as the RSS 2.0 feed for the syndication of hidden posts, but it does not take the
fullargument; Atom has in-build support for summaries versus fulltext, so I’ve used that instead. I’ve also decided I like Atom a lot more than RSS. - The links list in the bottom navigation block is now randomised.
Thrilling stuff!
And finally, I joined NaBloPoMo. Be afraid, be very afraid…
- « Previous
- Next »
Related Posts
Comments
-
Yay NaBloPoMo! I’m not doing it this year since I already did a blogathon in February, but maybe next year. I look forward to more posts from you.
-
Hah! To be honest I don’t know how I’m going to make it.
Lots of bad fiction, maybe?
-
You can talk about how you met up with Chris, Brittany, Jody and co. at the mall and you guys just chilled and gossiped about hair and stuff while drinking Starbucks and then later you guys went to Hot Topic and you found these great fishnet stockings but OMG Brittany was being such a bitch about it and said she things you looked terrible in it but it doesn’t matter anyway because you secretly hate her anyway but then you saw your crush totally walk by you guys and…
I mean who doesn’t want a month of that kinda entry??
-
Well maybe I will!
-
a) recruit kirky as a guest blogger.
b) why deep linking? -
b) why deep linking?
Er… because everyone else does it and baa-aa-aa?
-
baa-aa-aa is funnier than it should be.
-
Your related posts thing sounds much more intelligent than the way I did it. I just pulled the last three posts from the db that matched the categories the current post was in. I removed mine in the end in an attempt to reduce the amount of mysql connections on posts with a ridiculous amount of comments. (I get max_connection errors on any post with 70+ comments because of my ooold code.)
Actually, this post just makes me feel shittier for not updating my code that I’ve been telling myself I’d do for well over a year.
*emo cuts*
-
I was going to
MATCHagainst the post tags, as well but no-oo-oo-oo apparently MySQL is full of bugs and won’t let youMATCHacross aJOIN… la-aa-aa-aa-ame!
I get max_connection errors on any post with 70+ comments because of my ooold code.
Seriously? That’s really weird; you shouldn’t be getting that at all unless you’re constantly opening new connections to the DB all the time. You theoretically only need one connection per script execution (per user? I’m not sure if MySQL reuses the same connection across multiple users… I think it doesn’t). I mean, sk.log’s code is far from, erm, optimised in that regard but I’ve never seen a max connections error. Well, never one that was caused by sk.log at any rate.
Also; updating old code is totally over-rated. No-one can see it; just let them all assume it’s all 100% awesome.
-
I don’t know.. all I know is that it happens now and again and usually it’s when a post gets a large amount of comments. I don’t know whether it’s the code, or whether or not it’s because I have a table with just under 12,000 rows with the only index being the id (much more likely, AFAIK). I’ve also been told that my max connection limit (15) is pants but I’ve no idea what the ‘standard’ is so that could be total BS.
I just need to rip the whole lot out and start again. Better database structure, better indexing, less bloat in the code. However, finding time to do this when there are so many more appealing things to do (i.e. watching paint dry) is hard.
-
I’ve also been told that my max connection limit (15) is pants but I’ve no idea what the ‘standard’ is so that could be total BS.
Is that
max_connectionsormax_user_connections? I assume you’re on shared hosting;max_connectionsgoverns how many connections in total from all users can be made against MySQL (the default is 100) whilemax_user_connectionsis how many any one particular user can make (the default is ‘unlimited’, but obviously ceilinged bymax_connections).Because if you are on shared hosting and your
max_connectionsis the one set to 15… I’d be finding a new host. That’s some crazy resource throttling right there…However, finding time to do this when there are so many more appealing things to do (i.e. watching paint dry) is hard.
Word. Now that I’m out of uni, sk.log will never, ever get a major code re-write again.