PDA

View Full Version : added indexes


Reeve of shinra
17 Mar 2006, 14:29
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
17 Mar 2006, 16:15
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
17 Mar 2006, 19:48
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
17 Mar 2006, 20:08
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
21 Mar 2006, 20:45
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
21 Mar 2006, 21:39
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
23 Mar 2006, 23:03
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
24 Mar 2006, 08:33
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
24 Mar 2006, 16:48
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
24 Mar 2006, 19:16
ALTER TABLE post ADD INDEX ipaddress

Kevlar
03 Apr 2006, 14:55
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. :confused:

Xenon
03 Apr 2006, 16:07
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
03 Apr 2006, 19:07
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 ?

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 useTrue, but it wouldn't harm anything.

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
03 Apr 2006, 19:24
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.

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
04 Apr 2006, 16:07
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
15 May 2006, 14:53
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
05 Jun 2006, 05:30
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...


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
07 Jun 2006, 03:55
Pretty sure it's the second.

Spleasure
07 Jun 2006, 07:15
They work both. Note that you have to take care of adding the prefix if you use one.

BoardTracker
15 Jun 2006, 14:50
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.

Bulent Tekcan
21 Jul 2006, 12:37
How long time create a index ?

BoardTracker
21 Jul 2006, 13:30
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? :)

Bulent Tekcan
22 Jul 2006, 10:07
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