I Can't Believe the Difference in Speed

[Jolten]

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!

[soopa]

good stuff :devious:

[Gamingforce]

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.

[Velocd]

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

[Boofo]

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

[Natch]

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

[Boofo]

Originally Posted by Natch

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.

[Oblivion Knight]

Originally Posted by TTG

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

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

[MickDoneDee]

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.

PHP Code:

// Prefix that all vBulletin cookies will have
// For example
$cookieprefix = 'bb';

// ****** DEBUG MODE ON/OFF ******
// Turn debug on with '1' otherwise leave it blank to disable it.
$debug = '1';

if ($_SERVER['REMOTE_ADDR'] == 'your IP address')
{
$debug = 1;
} else {
$debug = 0;
} 

/*======================================================================*\
|| ####################################################################
|| # Downloaded: 19:34, Mon Apr 5th 2004
|| # CVS: $RCSfile: config.php.new,v $ - $Revision: 1.19 $
|| ####################################################################
\*======================================================================*/
?> 


Another feature that is activated with debug on is the Admin Help Manager which appears in the vBulletin Options menu within the Admin CP.

[MickDoneDee]

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.