Playing with the database
23:00 Sunday 27 Mar 05
Mark posted over at WebLogTools that he noticed an issue with his blog slowing down, and he pondered whether it was due to spam-karma. Having an excuse to mess with the database was a gift I was not about to ignore.
You can see the site stats (1,133 posts and 2,619 comments ) and that amounted, with the other bits, to an sql size of 3.9meg. The plugins I am running are immaterial - they have not changed in what I then did:
- dropped a couple of tables that were hangovers from previous plugins
- removed all the comments that had been flagged as spam
- optimised all the tables.
That netted me a gain of .4meg sql space. While that space gained is of no consequence in itself, it does mean that there is .4 of a meg less information (junk information) for the queries to run through. Is my blog running faster ? No idea, but it was fun playing :) Maybe if your database appears to be running slow, it’s worth a look ‘under the bonnet’ ?
(Note: If you are going to do this, backup okay ?)
1
Mark,
Removing tables or rows in your DB is unlikely to affect significantly its speed. Especially if we are talking about straight consultation of your pages (i.e. no INSERT/UPDATE, only SELECT)… MySQL is quite optimized to do queries within thousands or even millions of records without so much as a flinch…
My personal bet is that most of the speed hits you are seeing are PHP. If the DB plays a role, it would be only through the query number and its overhead (i.e.: each query, even when taking a ridiculously small amount of time, creates an overhead in the PHP-MySQL dialogue, that will eventually add up if you have 50-90 of them…).
Spam Karma will also definitely put an overhead. Although it should only be noticeable at all when posting a comment. I will soon work on improving things (for SK2), but so far it’s still reasonable, and lots of it is not under my control (the way the API works, plus reliance on external websites for IP checks)…
That being said, cleaning your data is never a bad idea… :)
04:17 Monday 28 Mar 05
2
drdave - with some subjects, I feel I fall squarely into the “dangerous” category :)
15:31 Monday 28 Mar 05
3
I should probably delete my spam comments here soon…I’m approaching 10,000 since I’ve been running 1.5, which is about a month and a half.
14:39 Friday 1 Apr 05