[Reeve of shinra]

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.

[The Prohacker]

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.

[Xenon]

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

[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.

[Erwin]

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.

[rossco_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.

[Kevlar]

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...

[007]

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.

[Fallback]

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?

[Paul M]

ALTER TABLE post ADD INDEX ipaddress

[Kevlar]

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.

[Xenon]

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

[Paul M]

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.

[The Prohacker]

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!

[Kevlar]

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.