I Can't Believe the Difference in Speed
 

Google has finally started crawling my board and guest traffic has started to increase. Things started to get rather sluggish. I upgraded my hosting plan to get more RAM and it helped somewhat, but not dramatically.

Then, I put an index on the dateline column in the posts table. OMG I can't believe the difference. Parts of the VBstats hack used to drive server load way up before. Now, I can actually use it without a wimper from the server. I can also use the hack that shows how many posts were made since the last visit now. Before, it just wasn't feasible.

My board isn't huge, but a decent size. If your medium to large board is starting to struggle, you might want to try this. I didn't do it in the past because I kept seeing Freddie telling people that it wasn't necessary. But I am really seeing a huge improvement.

Amy

If I say anything I am only referencing a non hacked forum when I say we are happy with the indexes that we have.

Will the only help with vbststs or will it also help if you are not running that? If so, what do we need to do to accomplish that?

What happens when two people post at the exact same second?

doesnt index make sure there are no duplicate values?

So you simple add an index to one column and it fixed all your problems :)?

How do we do this :D?

And your board is HUGEEE!! Do you make the ea games?

- miSt

Amy's forum has nothing to do with EA Games or anything lol.

Amy, please share how you did this ;).

I'm getting confused with electronic arts's forums :)

- miSt

1. We don't make EA games. However, a teenager joined once who thought we did and ripped us a new one because "our" games suck. Once we explained who we were, we figured we would never see him again. However, he still comes back from time to time when he needs help or wants to chat :D

2. Back on topic. The query I used was:

ALTER TABLE `post` ADD INDEX(`dateline`);

3. It helps quite a bit with vbstats, but I am also seeing a general speed increase. I did notice that takes a wee bit longer to post - but that is to be expected. From what I have read an index makes an insert take longer but a select faster.

4. Freddie, sorry I misunderstood.

I honestly don't believe we are a "huge" board. I prune periodically and keep things a reasonable size.

Amy

Hm, I tried this and it does seem to be significantly faster. I simply used PhpMyAdmin and clicked the "Index" link next to the appropriate column. :D

I run a small-ish board - 2200 members, 7900 threads and 104000 posts - on an IIS box - for those who are interested.

How does it makes things faster?

- miSt