IP Search freezes server

[Dennis Olson]

A few weeks ago, I'd posted that doing an IP seach ("Search for more IPs for this user") effectively froze my server, due to there being over 1 million posts on my forum. Fair enough.

I was also told to create an index on the IP addy. But no one ever told me:

1) Exactly how to do that (table name, field name, and SQL syntax)

2) What were the ramifications vis-a-vis board performance

3) Would any CODE CHANGES be required in order to "activate" this new index

I would appreciate these issues being addressed.

Thanks

[Lats]

1. ALTER TABLE user ADD INDEX (ipaddress)

2. Increase in performance when search on IP address.

3. None required, the database will use the index automatically.

[Dennis Olson]

Thanks Lats! One more question - do I have to shut the board down for this, or can I do it "live"? (I assume I have to shut down).

[Lats]

Umm, good question.

At a guess, it should only take around 5 - 10 seconds to perform that action on the number of posts you have, but to be on the safe side, close it down.

[Lats]

How good was my guess on this Dennis?

[Dennis Olson]

Haven't done it yet. My board is VERY busy, and I'm also mirrioring to another server. I need to run down an issue or two before I perform the SQL. I'm going to ask about the dropping of the board on a separate thread, in hops someone from vB can answer that issue specifically. I'll let you know how it works out...

Thanks for the info BTW!

[Dennis Olson]

To the vB development team - will this change negatively affect server performance?

[Dennis Olson]

Pleasse note: After index was installed, atempted an IP search - brought the board to a halt for two hours. What other schema changes are necessary to make this work properly?

Specifically, the query that croaks the database is the one where you searchfor OTHER MEMBERS using that IP address.

[Zachery]

It scans the whole post table. and depending on how large that is, this is where your problem lies.

[Dennis Olson]

I have over a million posts. I need to be able to perform that function. Do I need to index the IPaddress in the post table as well? If so, what are the ramifications of doing that?

[Lats]

Ouch!! That's bad news Dennis.

Just in case you're wondering what the command is...

ALTER TABLE post ADD INDEX (ipaddress)

To remove the other index, and put things back to 'normal'...

ALTER TABLE user DROP INDEX ipaddress

[Dennis Olson]

Thanks Lats. Before I change it, I want to know what issues might arise from the change.

[Dennis Olson]

bump for the day shift....

[Dennis Olson]

bump again....

[Scott MacVicar]

I'd close your forum while its being done as it will lock the entire post table as it builds the index. The only problem is that it will take a little longer to insert entries into the Post table as it rebuilds the index.