Register Members List Search Today's Posts Mark Forums Read

Reply
 
Mod Options
  #1  
Old 17 Mar 2006, 14:29
Reeve of shinra's Avatar
Reeve of shinra Reeve of shinra is offline
 
Join Date: Oct 2001
added indexes

Have you added any extra indexes to increase performance? I remember Erwin posted some a looong time ago that was a real help to my site. I just wish I could remember what they were.
__________________
Make someone smile today!
Reply With Quote
Comments
  #2  
Old 17 Mar 2006, 16:15
The Prohacker's Avatar
The Prohacker The Prohacker is offline
 
Join Date: Oct 2001
Real name: Mat Sumpter
We have added an index to the ipaddress column on the post table to help with IP searches and of course the full text index for full text searching. We have added a few others but I cannot remember them off the top of my head.
Reply With Quote
  #3  
Old 17 Mar 2006, 19:48
Xenon's Avatar
Xenon Xenon is offline
 
Join Date: Oct 2001
Real name: Stefan Kaeser
well everything is based on what indexes you really need, as extra indexes do also increase the work a db has to do when inserting/deleting
__________________
Unfortunatelly i am completely out of vbulletin buisness, so i won't upgrade any of my hacks anymore. Everyone who wants to upgrade my hacks or use part of my codes is allowed to do so, without needing any special permission by me anymore. You just have to mention my full name if you want to reuse some of my work, thats all i ask for.
Reply With Quote
  #4  
Old 17 Mar 2006, 20:08
Paul M's Avatar
Paul M Paul M is offline
 
Join Date: Sep 2004
Real name: Paul Marsden
Adding one to the IP address in the post table is a must on any large board - I really don't understand why it isn't default.

We have a few others due to some hacks - dateline and lastvisit on posts being two I believe.
__________________


Lead Developer, vBulletin.Org & vBulletin.Com
Please do not PM me about custom work - I no longer undertake any.

Note: I will not answer support questions via e-mail or PM - please use the relevant thread or forum.
Cable Forum - DigiGuide

Last edited by Paul M : 17 Mar 2006 at 20:11.
Reply With Quote
  #5  
Old 21 Mar 2006, 20:45
Erwin's Avatar
Erwin Erwin is offline
 
Join Date: Jan 2002
Originally Posted by Paul M
Adding one to the IP address in the post table is a must on any large board - I really don't understand why it isn't default.

We have a few others due to some hacks - dateline and lastvisit on posts being two I believe.
IP is a must for the post table, I agree.

That IP search in admincp is a server killer - I've offloaded it to a slave search server actually.
Reply With Quote
  #6  
Old 21 Mar 2006, 21:39
rossco_2005's Avatar
rossco_2005 rossco_2005 is offline
 
Join Date: Apr 2005
Only index i've added to my vb database that I remember is the IPadress one too.
But then there's also the indexes that have been added as part of other hack installs and upgrading the board.
__________________
Reply With Quote
  #7  
Old 23 Mar 2006, 23:03
Kevlar's Avatar
Kevlar Kevlar is offline
 
Join Date: Nov 2001
i've always wanted to add some extra indexes... but feared what would happen during forum upgrades. IP Address searching on our forum is almost a guaranteed server death...
__________________
KEVLAR
www.bimmerforums.com
Reply With Quote
  #8  
Old 24 Mar 2006, 08:33
007's Avatar
007 007 is offline
 
Join Date: Jan 2003
What eactly do you do to add an index? I'm assuming you are just adding a field to the table and specifying "index" or is there something else that has to be done for it to be beneficial?

Kevlar, I doubt adding it would effect upgrades. I have several custom fields added to different tables in my DB and upgrading has never been a problem.
__________________
~007
Reply With Quote
  #9  
Old 24 Mar 2006, 16:48
Fallback Fallback is offline
 
Join Date: May 2005
Originally Posted by Paul M
Adding one to the IP address in the post table is a must on any large board - I really don't understand why it isn't default.
How does one add an index to the IP address in the post table?
Reply With Quote
  #10  
Old 24 Mar 2006, 19:16
Paul M's Avatar
Paul M Paul M is offline
 
Join Date: Sep 2004
Real name: Paul Marsden
ALTER TABLE post ADD INDEX ipaddress
__________________


Lead Developer, vBulletin.Org & vBulletin.Com
Please do not PM me about custom work - I no longer undertake any.

Note: I will not answer support questions via e-mail or PM - please use the relevant thread or forum.
Cable Forum - DigiGuide
Reply With Quote
  #11  
Old 03 Apr 2006, 14:55
Kevlar's Avatar
Kevlar Kevlar is offline
 
Join Date: Nov 2001
Originally Posted by 007
Kevlar, I doubt adding it would effect upgrades. I have several custom fields added to different tables in my DB and upgrading has never been a problem.
Then my final question is ... why didn't the vB team add an index to that column already (especially since big boards all over suffer from this problem)?

That is the only thing stopping me at this point as it only seems logical to have an index on a column that is frequently used.
__________________
KEVLAR
www.bimmerforums.com
Reply With Quote
  #12  
Old 03 Apr 2006, 16:07
Xenon's Avatar
Xenon Xenon is offline
 
Join Date: Oct 2001
Real name: Stefan Kaeser
hmm, the question cannot be answered directly

but on the other hand, there are a lot of forums, which don't store the ips, and then and index wouldn't be of use

as i posted above, and index also has negative aspects, if it wouldn't you could always index every single field
__________________
Unfortunatelly i am completely out of vbulletin buisness, so i won't upgrade any of my hacks anymore. Everyone who wants to upgrade my hacks or use part of my codes is allowed to do so, without needing any special permission by me anymore. You just have to mention my full name if you want to reuse some of my work, thats all i ask for.
Reply With Quote
  #13  
Old 03 Apr 2006, 19:07
Paul M's Avatar
Paul M Paul M is offline
 
Join Date: Sep 2004
Real name: Paul Marsden
Originally Posted by Kevlar
Then my final question is ... why didn't the vB team add an index to that column already (especially since big boards all over suffer from this problem)
Perhaps no one has ever suggested it ?

Originally Posted by Xenon
but on the other hand, there are a lot of forums, which don't store the ips, and then and index wouldn't be of use
True, but it wouldn't harm anything.

Originally Posted by Xenon
as i posted above, and index also has negative aspects, if it wouldn't you could always index every single field
While indexing every field would ot make sense, there are a number of places that really could use them. The extra speed and saved resources on a select are usually worth the small amount of extra overhead when a record is added, and the space taken up. IMO people are often too afraid to add indexes when they really shouldn't be.
__________________


Lead Developer, vBulletin.Org & vBulletin.Com
Please do not PM me about custom work - I no longer undertake any.

Note: I will not answer support questions via e-mail or PM - please use the relevant thread or forum.
Cable Forum - DigiGuide
Reply With Quote
  #14  
Old 03 Apr 2006, 19:24
The Prohacker's Avatar
The Prohacker The Prohacker is offline
 
Join Date: Oct 2001
Real name: Mat Sumpter
Originally Posted by Xenon
but on the other hand, there are a lot of forums, which don't store the ips, and then and index wouldn't be of use

The default setting on vBulletin is to store IPs and not display them publicly. Most people don't change default settings so an index could help everyone.

Originally Posted by Paul M
While indexing every field would ot make sense, there are a number of places that really could use them. The extra speed and saved resources on a select are usually worth the small amount of extra overhead when a record is added, and the space taken up. IMO people are often too afraid to add indexes when they really shouldn't be.
Couldn't put it any better!
Reply With Quote
  #15  
Old 04 Apr 2006, 16:07
Kevlar's Avatar
Kevlar Kevlar is offline
 
Join Date: Nov 2001
Index added ... no ill effects found (other than me being unable to wait patiently while the alter table command ran). IP searches are much faster now... almost instantaneous.
__________________
KEVLAR
www.bimmerforums.com
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Mod Options

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
Forum Jump


New To Site? Need Help?

All times are GMT. The time now is 10:08.

Layout Options | Width: Wide Color: