Improvement for bannedip table.

[rmccny]

Here is a small improvement we made in our bannedips table. It involves a change to the table structure so please proceed at your own risk.

 

What this little enhancement does is speed up access to the bannedips table (which we use in other areas outside of WHMCS) plus it also makes searching for IPs and ranges of IPs much easier. This enhancement adds a 32 bit unsigned INT column to bannedips and fills the column with numeric representations of each IP address. You must be running MySQL 5.0.3 or later for this to work!

 

Here are the queries to do it:

 

Add the field to the table:

ALTER TABLE `tblbannedips` ADD `ipn` INT( 11 ) UNSIGNED NOT NULL AFTER `ip`;

Fill the new field with existing IP addresses:

UPDATE `tblbannedips` SET ipn=INET_ATON(ip);

Index the new field:

ALTER TABLE `tblbannedips` ADD INDEX ( `ipn` );

Now for a little trigger magic, since WHMCS doesn't understand how to fill this field, this will create a trigger that will run prior to ANY INSERT operation to fill the field with the 32 bit IP address. You need at least MySQL 5.0.3 for this:

CREATE TRIGGER ins_ipn BEFORE INSERT ON tblbannedips FOR EACH ROW SET NEW.ipn=INET_ATON(NEW.ip);

Now you will have a 32 bit numeric value for every IP address. Here are some sample queries for searching...

 

Query by address:

SELECT * FROM tblbannedips WHERE ipn = INET_ATON('123.123.123.123');

Query all addresses between 123.1.0.0 and 123.3.255.255:

SELECT * FROM tblbannedips WHERE ipn > INET_ATON('123.1.0.0') AND ipn < INET_ATON('123.3.255.255');

In the end however, the queries are only a little bit faster.

 

SELECT SQL_NO_CACHE * FROM tblbannedips WHERE ipn = INET_ATON('123.123.123.123');  Query took 0.0004 sec
SELECT SQL_NO_CACHE * FROM tblbannedips WHERE ip = '123.123.123.123';  Query took 0.0006 sec

Finally, converting back although not needed since the text IP column is still filled:

SELECT INET_NTOA(ipn) FROM tblbannedips WHERE id=30;

 

Not exactly earth-shattering, but hope someone finds it useful.

[vchosting]

What version of WHMCS was this for

[rmccny]

I believe we were running 4.1.1 when I posted that. I'm using this on 4.1.2 right now without any problems.