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.

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.

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

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.

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

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

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.

How does one add an index to the IP address in the post table?

ALTER TABLE post ADD INDEX ipaddress

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

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 ;)

Perhaps no one has ever suggested it ?

True, but it wouldn't harm anything.

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

Couldn't put it any better!

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.

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.

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



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

Pretty sure it's the second.

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

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.

How long time create a index ?

It depends how big is the table and what Index type it is.
for non FTS index, it is usually not too long. Only if you have a VERY big table with tens of millions of records, it may take more than a minute. Huge tables, might take more, maybe 5 or even 10 minutes. But I mean HUGE tables.

P.S.
Did you submit your board to Boardtracker for inclusion? :)

Himmmm OK

Our post is 2.300.000 :) And finished 7 minutes my dual opteron machines ;) ,also now very fast search IP adress and without any problem.

Normaly our boards use a big-boards.com,but manytimes they are not updates our data,for example alexa rating.But I'll invest'gation your service ;)

Thanks