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

It makes queries to the post table faster because the index orders the posts. So, queries that look for posts since a given date are significantly sped up.

Does it do it retroactively or just with posts made after adding the query?

an DB index is for all data in the table, not just for the new ones ;)

btw. to speed up the getnew post you can add also an index to thread.lastpost :)

it reduced the gen time on vb.org from .27 to .07 ;)

Good tips. :)

Any other places where an index might help speed things up?

Any drawbacks to doing this? Can it be un-done?

@boofo: currently i don't know any others, but i'm sure there can be some.
but it's also depending on your hacks and so on, where you could need an index :)

@Dolby: well each index requires DB Space
you can see on the tablestatistics how much space the data takes and how much the indexes take.
yes it can be un-done :)

FWIW, I added indexes to post.dateline, thread.lastpost as well as thread.lastposter - did speed things up at my site. :)

How much overhead does each index add to the db? Just curious. ;)

Is there any downsides to doing this ?

Also what should i do to implement this ?
Basic instructions would be appreciated ( nooB here )

cinq, everything is exactly mentioned here, i posted the downsides 3 posts above.

Dbspace ie. the database just gets bigger right ?
Will the increase be so substantial that it is something to worry about ?

So the command i use in phpmyadmin is run this query ?

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

Just wish to confirm and not do something wrong and foul up the board :)

Thanks

Since it can be un-done ( looks like you just go into phpmyadmin and click on the drop button where it lists the indexs) I tried it and vbstats show some increase in speed. I was at about "Page generated in .8xxx" and its now about "Page generated in .06xxx" on my forum index.

Currently have 112 online browseing.

Downsize to too much indexes is not just extra database space. While adding an index to thread.lastpost speeds up Get New Posts, it also slows down New Thread/Reply.

Hmmm seems just as fast if not faster on my busy board to make a new thread or reply. *shrug*

As most of the database load is from guests browsing the forums, rather than posting, the trade-off is well worth it.

I added the ALTER TABLE `post` ADD INDEX(`dateline`); and must admit my board seem to load a lot faster.

What are the codes to add for post.dateline, thread.lastpost as well as thread.lastposter

Can this be done with the board open and users using the vB?

yes :)

Great Tip! Amazing speedup!!!

Thanks-- I just did this to both my sites and it is awesome and so dam simple...

Wow. Although I wasn't at all unhappy with the speed of my tiny little forum adding indexes to post.dateline, thread.lastpost, and thread.lastposter has made pages load exceptionally fast. Thanks for this Amy!

good stuff :devious:

I just added an index to post.dateline and thread.lastpost. Speed seems a little faster and database insertions into the post/thread tables don't seem too bad. My board has 150-300 users, on average and has around 850,000 posts.

I would recommend this simple modification to anyone.

In addition to regularly optimizing your index tables, indexing can be a big help on columns when quering.

More information:
http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html

Another good read on general database optimization:
http://dev.mysql.com/doc/mysql/en/Op...Structure.html

In phpmyadmin, how can you tell if something already has an index for it?

In the Structure layout, roll down the the bottom - there is an Index summary table below the main layout...

Thank you, sir. I needed to check if I had more then 1 index for a couple of these and I did. Now all is well. ;)

ALTER TABLE `thread` ADD INDEX(`lastpost`);

ALTER TABLE `thread` ADD INDEX(`lastposter`);



Strangely, my forum seems to load faster, but the Microstats don't reflect this..

Viewing a thread before:
Page generated in 0.11889291 seconds (92.06% PHP - 7.94% MySQL) with 13 queries

Viewing a thread after:
Page generated in 0.50758195 seconds (96.13% PHP - 3.87% MySQL) with 13 queries

I indexed the following as mentioned in the previous posts:

post.dateline
thread.lastpost
thread.lastposter

Adding indexes for thread.lastpost and thread.lastposter only increased the space usage by about 10,000 bytes.

I didn't notice a discernable difference in speed when loading the index page or forumdisplay page.

I turned the debug mode on in the config.php file so that the controls appeared at the bottom of the forum pages. Then I clicked (Explain) which loaded http://www.myforum.com/?explain=1

This detailed all the Queries that were run. I checked through this for the index page and forumdisplay page. I couldn't find any reference to post.dateline, thread.lastpost or thread.lastposter. So page load speed for the index page or forumdisplay page can't be influenced by those three new indexes. Question is: which pages do load faster as a result of those three indexes?

If your config.php file doesn't already contain $debug, add it to the bottom of the file as I've shown here. To prevent other users from accessing it add your IP address where I've indicated. You can get your IP address from Who's Online if you are logged on.

https://vborg.vbsupport.ru/external/2011/01/19.gif Another feature that is activated with debug on is the Admin Help Manager which appears in the vBulletin Options menu within the Admin CP.

To add an index in your phpmyadmin the attached images will help to explain it.

Load the page which displays all the tables. See part of this page in PhpMyAdmin 2001.jpg

Notice that each table has a Properties column. Choose your table and click Properties in that row. See PhpMyAdmin 9001s.jpg for the Post table.

Notice that each property has an Index column. You can also see a table at the bottom which shows existing indexes. You don't want to create one if it already exists. Choose your property and click Index in that row.

See PhpMyAdmin 11001.jpg that shows a message to say the index has been added.

See PhpMyAdmin 12001.jpg that shows that the property "dateline" has been added to the list of indexes.

Hope this helps make sense of it all.