Speedup IP Searching?

[edbri871]

Is there a way to speedup searching in the admincp by IP address?

I was looking to remove some from the database, but realized they are part of the posts table. :ermm:

The board has over 1,000,000 posts, and over 90,000 members altho a lot of those might be pruned soon.

Some ideas for improving the IP search in admincp would be great, or if I could be pointed to some mods for speeding it up that would be great too. :)

[tamarian]

Easy. If you have a lot of disk space, add an index to your post table on the ip address field. It will run real fast. Big board generally need this.

[edbri871]

Thanks for the reply. :)

Can you let me know a bit more about what this is going to do? How does it speed it up, and why will I need more disc space (i'll have to ask the board owner about that one)?

[tamarian]

Thanks for the reply. :)

Can you let me know a bit more about what this is going to do? How does it speed it up, and why will I need more disc space (i'll have to ask the board owner about that one)?

Well, the post table is huge, usually the biggest in your vB database. If there is no index on the IP field, the query will have to search the entire post table, without an index. If you add an index by IP address, it will know where to look for, as they are already mapped. This will speed the IP search significantly. Without an IP index, your search will be slower as the post table gets bigger.

The disk space is required whenever you add an index, since the index will need to be stored on the hard disk. It will take a percentage of the size of the post table, so it's not too big (not bigger than the actual post table)

[edbri871]

OK Thanks a lot for your help. :D

I will go ahead and try this, then post back with the results. :)

It worked! :D Thanks a lot for the help!

[beano33]

OK Thanks a lot for your help. :D

I will go ahead and try this, then post back with the results. :)

It worked! :D Thanks a lot for the help!

I could really use this too. Can you tell us exactly what you did? Thanks!

[tamarian]

I could really use this too. Can you tell us exactly what you did? Thanks!

To add an index on ip adresses in the post table, you can do it in a couple of ways.

1. If you have phpMyAdmin, just scroll and click on the post table, which will show the table's fields. Then on the ipaddress field, click the index button.

2. Or, from the admin panel MySQL tool, enter this query:
ALTER TABLE 'post' ADD INDEX ( `ipaddress` )
(add the table prefix to 'post' if you use one.

[beano33]

Thanks, it works great.

[beano33]

Is there a similar way to add an index to the table that's searched for users online? It would be nice to have it on without killing the server.