Playing with the database

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 ?)

3 thoughts on “Playing with the database

  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… :)

Leave a Reply

Your email address will not be published. Required fields are marked *