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.

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

[Spleasure]

If the vB team would add the index in an upcoming upgrade that upgrade would become problematic for large boards without the index. It has to be introduced very gently to not upset the customers.

[DementedMindz]

ok question if i was to add this add an index to the IP address in the post table and if i upgrade when 3.6 comes out will there be any changes i need to do... just making sure down the line i dont get no errors thanks...

Originally Posted by Paul M

ALTER TABLE post ADD INDEX ipaddress

also i seen this posted before is pauls the right one or is this one the right one?

ALTER TABLE `post` ADD INDEX `ipaddress` ( `ipaddress` )

[cscgal]

Pretty sure it's the second.

[Spleasure]

They work both. Note that you have to take care of adding the prefix if you use one.

[BoardTracker]

If I may give a few highlights on indexes and databases (MySQL specifically a well since most of you use that one)

Indexes as Paul M indicated are not really harmful when not used, and are very useful when they are used (as Kevlar noticed).

However, you should be aware of the downside of an unused index.
1. Indexes take time to create. Not only during altering a table but also whenever a new record is added to the table. So bear that in mind. Although, when adding a simple index like IP row index, the effect of it on insert is usually very mild.
2. Indexes do take space. Some more, some less. If you add more and more indexes, especially if they are not used, they can eventually take more space than the data itself. In some cases this is even the desired situation.
3. Indexes can corrupt. Although tables don't get corrupted on a daily basis (thank god), it can (and sometimes does) happen. The less indexes you have, the better in that respect .

So I would say that if an index is not needed, don't add it. As for whether or not VB should add it.. if there are built-in features that do a cumbersome select on a field in what usually gets to be a big table, an index for the field is most likely needed.

Lastly.. there is not much you can do though with regards to the regular full-text search on the database level. This is because databases were not really designed for FTS (FullText search) indexing. It works, but usually for small boards. When your board gets big, FTS will probably grind your servers down.

In case you have that problem, a mod we developed for board owners can solve that problem, by using BoardTracker's search implemented into the board. You can check out boards.ie or rpg.net for examples which use it.