PostgreSQL

[rvt]

Has anybody ever tried to run the DB on postgreSQL and did speed comparisons??

I am planning to use it on PostgreSQL but store all 256 tables into one large tables and putting my index on a seperated disk.

Also, I want to see if the IP datatype in postgresql can be of any help speeding up the search process.

Any ideas, thoughts etc?

cheers,
Ries

[bfolkens]

Has anybody ever tried to run the DB on postgreSQL and did speed comparisons??

I am planning to use it on PostgreSQL but store all 256 tables into one large tables and putting my index on a seperated disk.

Also, I want to see if the IP datatype in postgresql can be of any help speeding up the search process.


Any ideas, thoughts etc?

cheers,
Ries

There's actually a nice format you can use to store it in if you have one long table - it's called an IP Number

I think mysql had functions built in to convert the two (again, I think) - not sure about postgresql though. The IP number is basically a dword which is converted from the IP address quadrants.

Code: Select all

216.177.121.11 = 3635509515
((216*256 + 177) * 256 + 121) * 256 + 11 = 3635509515


This is a nice discussion on the ways you can represent a dotted quad:
http://lists.sans.org/pipermail/list/2003-August/058208.html

Basically, using this idea you would make that your primary, clustered key and end up with another fast way to get at the data, knowing the ip address. Then if you provided that across from the cityid, index the cityid and you've got a fast way to do the reverse.

[rvt]

Has anybody ever tried to run the DB on postgreSQL and did speed comparisons??

I am planning to use it on PostgreSQL but store all 256 tables into one large tables and putting my index on a seperated disk.

Also, I want to see if the IP datatype in postgresql can be of any help speeding up the search process.


Any ideas, thoughts etc?

cheers,
Ries

There's actually a nice format you can use to store it in if you have one long table - it's called an IP Number

I think mysql had functions built in to convert the two (again, I think) - not sure about postgresql though. The IP number is basically a dword which is converted from the IP address quadrants.

Code: Select all

216.177.121.11 = 3635509515
((216*256 + 177) * 256 + 121) * 256 + 11 = 3635509515


This is a nice discussion on the ways you can represent a dotted quad:
http://lists.sans.org/pipermail/list/2003-August/058208.html

Basically, using this idea you would make that your primary, clustered key and end up with another fast way to get at the data, knowing the ip address. Then if you provided that across from the cityid, index the cityid and you've got a fast way to do the reverse.

Indeed,

I want to see if I can make it in such a way that we can use the montly updates, but making it so that with a trigger for example we can put teh IP data into one single table.

I am guessing that such a lookup might be faster when doing bulk lookups.

The problem I start to face is taht when doing a lot of lookup, the total time is quite long.. Butting everything into one single table might speedup the process of such.

I ain't sure about mysql capabilties, but I would guess since everything is 'native' mysql, there isn't such a option (I ain't a mysql guru) I have been working more with postgresql in the past.....

I'll go continue and try to make such tests...

ries

[bfolkens]

Has anybody ever tried to run the DB on postgreSQL and did speed comparisons??

I am planning to use it on PostgreSQL but store all 256 tables into one large tables and putting my index on a seperated disk.

Also, I want to see if the IP datatype in postgresql can be of any help speeding up the search process.


Any ideas, thoughts etc?

cheers,
Ries

There's actually a nice format you can use to store it in if you have one long table - it's called an IP Number

I think mysql had functions built in to convert the two (again, I think) - not sure about postgresql though. The IP number is basically a dword which is converted from the IP address quadrants.

Code: Select all

216.177.121.11 = 3635509515
((216*256 + 177) * 256 + 121) * 256 + 11 = 3635509515


This is a nice discussion on the ways you can represent a dotted quad:
http://lists.sans.org/pipermail/list/2003-August/058208.html

Basically, using this idea you would make that your primary, clustered key and end up with another fast way to get at the data, knowing the ip address. Then if you provided that across from the cityid, index the cityid and you've got a fast way to do the reverse.

Indeed,

I want to see if I can make it in such a way that we can use the montly updates, but making it so that with a trigger for example we can put teh IP data into one single table.

I am guessing that such a lookup might be faster when doing bulk lookups.

The problem I start to face is taht when doing a lot of lookup, the total time is quite long.. Butting everything into one single table might speedup the process of such.

I ain't sure about mysql capabilties, but I would guess since everything is 'native' mysql, there isn't such a option (I ain't a mysql guru) I have been working more with postgresql in the past.....

I'll go continue and try to make such tests...

ries

You know, one thing that makes bulk lookups nice in a 1-large-table-with-ip-dword approach is that you can do something like:

Code: Select all

SELECT ipnumber,a,b,c,d,cityid FROM big_table WHERE ipnumber IN (32948235,23698367,12950002,5938763,032938597)


And prevent the latency in generating 'n' number of SELECT queries to and from the database (esp. if it's not a local database)... just a thought

[rvt]

Has anybody ever tried to run the DB on postgreSQL and did speed comparisons??

I am planning to use it on PostgreSQL but store all 256 tables into one large tables and putting my index on a seperated disk.

Also, I want to see if the IP datatype in postgresql can be of any help speeding up the search process.


Any ideas, thoughts etc?

cheers,
Ries

There's actually a nice format you can use to store it in if you have one long table - it's called an IP Number

I think mysql had functions built in to convert the two (again, I think) - not sure about postgresql though. The IP number is basically a dword which is converted from the IP address quadrants.

Code: Select all

216.177.121.11 = 3635509515
((216*256 + 177) * 256 + 121) * 256 + 11 = 3635509515


This is a nice discussion on the ways you can represent a dotted quad:
http://lists.sans.org/pipermail/list/2003-August/058208.html

Basically, using this idea you would make that your primary, clustered key and end up with another fast way to get at the data, knowing the ip address. Then if you provided that across from the cityid, index the cityid and you've got a fast way to do the reverse.

Indeed,

I want to see if I can make it in such a way that we can use the montly updates, but making it so that with a trigger for example we can put teh IP data into one single table.

I am guessing that such a lookup might be faster when doing bulk lookups.

The problem I start to face is taht when doing a lot of lookup, the total time is quite long.. Butting everything into one single table might speedup the process of such.

I ain't sure about mysql capabilties, but I would guess since everything is 'native' mysql, there isn't such a option (I ain't a mysql guru) I have been working more with postgresql in the past.....

I'll go continue and try to make such tests...

ries

You know, one thing that makes bulk lookups nice in a 1-large-table-with-ip-dword approach is that you can do something like:

Code: Select all

SELECT ipnumber,a,b,c,d,cityid FROM big_table WHERE ipnumber IN (32948235,23698367,12950002,5938763,032938597)


And prevent the latency in generating 'n' number of SELECT queries to and from the database (esp. if it's not a local database)... just a thought

Yeaaa indeed...
I have the tables converted to postgresql now.
I created this rule:
CREATE RULE ip4_$i_ins AS ON INSERT TO ip4_$i
DO INSTEAD INSERT INTO ip4 (ip, country, city,cron)
VALUES ( trim('1' || '.' || NEW.b || '.' || NEW.c || '.' || '0')::cidr, NEW.country, NEW.city, new.cron);

And this table:
CREATE TABLE ip4
(
ip cidr,
country INTEGER NOT NULL,
city INTEGER NOT NULL,
cron TIMESTAMP NOT NULL
);
CREATE UNIQUE INDEX ip4_ip_idx ON ip4(ip);
CREATE INDEX ip4_country_idx ON ip4(country);
CREATE INDEX ip4_city_idx ON ip4(city);
CREATE INDEX ip4_cron_idx ON ip4(cron);

I need to check for update/delete rules though... I shall download one of the incremental files to see what's in there...

Ries

[Guest]

Code: Select all

216.177.121.11 = 3635509515
((216*256 + 177) * 256 + 121) * 256 + 11 = 3635509515


Nice idea ... before coding though please confirm the formula ... see:
http://ip-to-country.webhosting.info/node/view/261

Also, since you are ignoring the "D" value it should be able to be shortened to:

A x (256*256) + B x 256 + C

this would make the max number: 16,843,008

Great job on this site and project.

Rob

[yaggier]

Hey Brad,

Give this a try and see how it does for your bulk queries ....

Create a new single table:

CREATE TABLE `ipAll` (
`ipkey` bigint(9) NOT NULL default '0',
`country` smallint(5) default NULL,
`city` smallint(5) default NULL,
PRIMARY KEY (`ipkey`)
) TYPE=MyISAM;



Then Insert data into using your exising tables:
insert into ipAll select (0*256*256) + (b*256) + c as ipkey, country, city from ip4_0;
insert into ipAll select (1*256*256) + (b*256) + c as ipkey, country, city from ip4_1;
insert into ipAll select (2*256*256) + (b*256) + c as ipkey, country, city from ip4_2;
insert into ipAll select (3*256*256) + (b*256) + c as ipkey, country, city from ip4_3;
insert into ipAll select (4*256*256) + (b*256) + c as ipkey, country, city from ip4_4;
insert into ipAll select (5*256*256) + (b*256) + c as ipkey, country, city from ip4_5;
insert into ipAll select (6*256*256) + (b*256) + c as ipkey, country, city from ip4_6;
insert into ipAll select (7*256*256) + (b*256) + c as ipkey, country, city from ip4_7;
insert into ipAll select (8*256*256) + (b*256) + c as ipkey, country, city from ip4_8;
insert into ipAll select (9*256*256) + (b*256) + c as ipkey, country, city from ip4_9;
insert into ipAll select (10*256*256) + (b*256) + c as ipkey, country, city from ip4_10;
insert into ipAll select (11*256*256) + (b*256) + c as ipkey, country, city from ip4_11;
insert into ipAll select (12*256*256) + (b*256) + c as ipkey, country, city from ip4_12;
insert into ipAll select (13*256*256) + (b*256) + c as ipkey, country, city from ip4_13;
insert into ipAll select (14*256*256) + (b*256) + c as ipkey, country, city from ip4_14;
insert into ipAll select (15*256*256) + (b*256) + c as ipkey, country, city from ip4_15;
insert into ipAll select (16*256*256) + (b*256) + c as ipkey, country, city from ip4_16;
insert into ipAll select (17*256*256) + (b*256) + c as ipkey, country, city from ip4_17;
insert into ipAll select (18*256*256) + (b*256) + c as ipkey, country, city from ip4_18;
insert into ipAll select (19*256*256) + (b*256) + c as ipkey, country, city from ip4_19;
insert into ipAll select (20*256*256) + (b*256) + c as ipkey, country, city from ip4_20;
insert into ipAll select (21*256*256) + (b*256) + c as ipkey, country, city from ip4_21;
insert into ipAll select (22*256*256) + (b*256) + c as ipkey, country, city from ip4_22;
insert into ipAll select (23*256*256) + (b*256) + c as ipkey, country, city from ip4_23;
insert into ipAll select (24*256*256) + (b*256) + c as ipkey, country, city from ip4_24;
insert into ipAll select (25*256*256) + (b*256) + c as ipkey, country, city from ip4_25;
insert into ipAll select (26*256*256) + (b*256) + c as ipkey, country, city from ip4_26;
insert into ipAll select (27*256*256) + (b*256) + c as ipkey, country, city from ip4_27;
insert into ipAll select (28*256*256) + (b*256) + c as ipkey, country, city from ip4_28;
insert into ipAll select (29*256*256) + (b*256) + c as ipkey, country, city from ip4_29;
insert into ipAll select (30*256*256) + (b*256) + c as ipkey, country, city from ip4_30;
insert into ipAll select (31*256*256) + (b*256) + c as ipkey, country, city from ip4_31;
insert into ipAll select (32*256*256) + (b*256) + c as ipkey, country, city from ip4_32;
insert into ipAll select (33*256*256) + (b*256) + c as ipkey, country, city from ip4_33;
insert into ipAll select (34*256*256) + (b*256) + c as ipkey, country, city from ip4_34;
insert into ipAll select (35*256*256) + (b*256) + c as ipkey, country, city from ip4_35;
insert into ipAll select (36*256*256) + (b*256) + c as ipkey, country, city from ip4_36;
insert into ipAll select (37*256*256) + (b*256) + c as ipkey, country, city from ip4_37;
insert into ipAll select (38*256*256) + (b*256) + c as ipkey, country, city from ip4_38;
insert into ipAll select (39*256*256) + (b*256) + c as ipkey, country, city from ip4_39;
insert into ipAll select (40*256*256) + (b*256) + c as ipkey, country, city from ip4_40;
insert into ipAll select (41*256*256) + (b*256) + c as ipkey, country, city from ip4_41;
insert into ipAll select (42*256*256) + (b*256) + c as ipkey, country, city from ip4_42;
insert into ipAll select (43*256*256) + (b*256) + c as ipkey, country, city from ip4_43;
insert into ipAll select (44*256*256) + (b*256) + c as ipkey, country, city from ip4_44;
insert into ipAll select (45*256*256) + (b*256) + c as ipkey, country, city from ip4_45;
insert into ipAll select (46*256*256) + (b*256) + c as ipkey, country, city from ip4_46;
insert into ipAll select (47*256*256) + (b*256) + c as ipkey, country, city from ip4_47;
insert into ipAll select (48*256*256) + (b*256) + c as ipkey, country, city from ip4_48;
insert into ipAll select (49*256*256) + (b*256) + c as ipkey, country, city from ip4_49;
insert into ipAll select (50*256*256) + (b*256) + c as ipkey, country, city from ip4_50;
insert into ipAll select (51*256*256) + (b*256) + c as ipkey, country, city from ip4_51;
insert into ipAll select (52*256*256) + (b*256) + c as ipkey, country, city from ip4_52;
insert into ipAll select (53*256*256) + (b*256) + c as ipkey, country, city from ip4_53;
insert into ipAll select (54*256*256) + (b*256) + c as ipkey, country, city from ip4_54;
insert into ipAll select (55*256*256) + (b*256) + c as ipkey, country, city from ip4_55;
insert into ipAll select (56*256*256) + (b*256) + c as ipkey, country, city from ip4_56;
insert into ipAll select (57*256*256) + (b*256) + c as ipkey, country, city from ip4_57;
insert into ipAll select (58*256*256) + (b*256) + c as ipkey, country, city from ip4_58;
insert into ipAll select (59*256*256) + (b*256) + c as ipkey, country, city from ip4_59;
insert into ipAll select (60*256*256) + (b*256) + c as ipkey, country, city from ip4_60;
insert into ipAll select (61*256*256) + (b*256) + c as ipkey, country, city from ip4_61;
insert into ipAll select (62*256*256) + (b*256) + c as ipkey, country, city from ip4_62;
insert into ipAll select (63*256*256) + (b*256) + c as ipkey, country, city from ip4_63;
insert into ipAll select (64*256*256) + (b*256) + c as ipkey, country, city from ip4_64;
insert into ipAll select (65*256*256) + (b*256) + c as ipkey, country, city from ip4_65;
insert into ipAll select (66*256*256) + (b*256) + c as ipkey, country, city from ip4_66;
insert into ipAll select (67*256*256) + (b*256) + c as ipkey, country, city from ip4_67;
insert into ipAll select (68*256*256) + (b*256) + c as ipkey, country, city from ip4_68;
insert into ipAll select (69*256*256) + (b*256) + c as ipkey, country, city from ip4_69;
insert into ipAll select (70*256*256) + (b*256) + c as ipkey, country, city from ip4_70;
insert into ipAll select (71*256*256) + (b*256) + c as ipkey, country, city from ip4_71;
insert into ipAll select (72*256*256) + (b*256) + c as ipkey, country, city from ip4_72;
insert into ipAll select (73*256*256) + (b*256) + c as ipkey, country, city from ip4_73;
insert into ipAll select (74*256*256) + (b*256) + c as ipkey, country, city from ip4_74;
insert into ipAll select (75*256*256) + (b*256) + c as ipkey, country, city from ip4_75;
insert into ipAll select (76*256*256) + (b*256) + c as ipkey, country, city from ip4_76;
insert into ipAll select (77*256*256) + (b*256) + c as ipkey, country, city from ip4_77;
insert into ipAll select (78*256*256) + (b*256) + c as ipkey, country, city from ip4_78;
insert into ipAll select (79*256*256) + (b*256) + c as ipkey, country, city from ip4_79;
insert into ipAll select (80*256*256) + (b*256) + c as ipkey, country, city from ip4_80;
insert into ipAll select (81*256*256) + (b*256) + c as ipkey, country, city from ip4_81;
insert into ipAll select (82*256*256) + (b*256) + c as ipkey, country, city from ip4_82;
insert into ipAll select (83*256*256) + (b*256) + c as ipkey, country, city from ip4_83;
insert into ipAll select (84*256*256) + (b*256) + c as ipkey, country, city from ip4_84;
insert into ipAll select (85*256*256) + (b*256) + c as ipkey, country, city from ip4_85;
insert into ipAll select (86*256*256) + (b*256) + c as ipkey, country, city from ip4_86;
insert into ipAll select (87*256*256) + (b*256) + c as ipkey, country, city from ip4_87;
insert into ipAll select (88*256*256) + (b*256) + c as ipkey, country, city from ip4_88;
insert into ipAll select (89*256*256) + (b*256) + c as ipkey, country, city from ip4_89;
insert into ipAll select (90*256*256) + (b*256) + c as ipkey, country, city from ip4_90;
insert into ipAll select (91*256*256) + (b*256) + c as ipkey, country, city from ip4_91;
insert into ipAll select (92*256*256) + (b*256) + c as ipkey, country, city from ip4_92;
insert into ipAll select (93*256*256) + (b*256) + c as ipkey, country, city from ip4_93;
insert into ipAll select (94*256*256) + (b*256) + c as ipkey, country, city from ip4_94;
insert into ipAll select (95*256*256) + (b*256) + c as ipkey, country, city from ip4_95;
insert into ipAll select (96*256*256) + (b*256) + c as ipkey, country, city from ip4_96;
insert into ipAll select (97*256*256) + (b*256) + c as ipkey, country, city from ip4_97;
insert into ipAll select (98*256*256) + (b*256) + c as ipkey, country, city from ip4_98;
insert into ipAll select (99*256*256) + (b*256) + c as ipkey, country, city from ip4_99;
insert into ipAll select (100*256*256) + (b*256) + c as ipkey, country, city from ip4_100;
insert into ipAll select (101*256*256) + (b*256) + c as ipkey, country, city from ip4_101;
insert into ipAll select (102*256*256) + (b*256) + c as ipkey, country, city from ip4_102;
insert into ipAll select (103*256*256) + (b*256) + c as ipkey, country, city from ip4_103;
insert into ipAll select (104*256*256) + (b*256) + c as ipkey, country, city from ip4_104;
insert into ipAll select (105*256*256) + (b*256) + c as ipkey, country, city from ip4_105;
insert into ipAll select (106*256*256) + (b*256) + c as ipkey, country, city from ip4_106;
insert into ipAll select (107*256*256) + (b*256) + c as ipkey, country, city from ip4_107;
insert into ipAll select (108*256*256) + (b*256) + c as ipkey, country, city from ip4_108;
insert into ipAll select (109*256*256) + (b*256) + c as ipkey, country, city from ip4_109;
insert into ipAll select (110*256*256) + (b*256) + c as ipkey, country, city from ip4_110;
insert into ipAll select (111*256*256) + (b*256) + c as ipkey, country, city from ip4_111;
insert into ipAll select (112*256*256) + (b*256) + c as ipkey, country, city from ip4_112;
insert into ipAll select (113*256*256) + (b*256) + c as ipkey, country, city from ip4_113;
insert into ipAll select (114*256*256) + (b*256) + c as ipkey, country, city from ip4_114;
insert into ipAll select (115*256*256) + (b*256) + c as ipkey, country, city from ip4_115;
insert into ipAll select (116*256*256) + (b*256) + c as ipkey, country, city from ip4_116;
insert into ipAll select (117*256*256) + (b*256) + c as ipkey, country, city from ip4_117;
insert into ipAll select (118*256*256) + (b*256) + c as ipkey, country, city from ip4_118;
insert into ipAll select (119*256*256) + (b*256) + c as ipkey, country, city from ip4_119;
insert into ipAll select (120*256*256) + (b*256) + c as ipkey, country, city from ip4_120;
insert into ipAll select (121*256*256) + (b*256) + c as ipkey, country, city from ip4_121;
insert into ipAll select (122*256*256) + (b*256) + c as ipkey, country, city from ip4_122;
insert into ipAll select (123*256*256) + (b*256) + c as ipkey, country, city from ip4_123;
insert into ipAll select (124*256*256) + (b*256) + c as ipkey, country, city from ip4_124;
insert into ipAll select (125*256*256) + (b*256) + c as ipkey, country, city from ip4_125;
insert into ipAll select (126*256*256) + (b*256) + c as ipkey, country, city from ip4_126;
insert into ipAll select (127*256*256) + (b*256) + c as ipkey, country, city from ip4_127;
insert into ipAll select (128*256*256) + (b*256) + c as ipkey, country, city from ip4_128;
insert into ipAll select (129*256*256) + (b*256) + c as ipkey, country, city from ip4_129;
insert into ipAll select (130*256*256) + (b*256) + c as ipkey, country, city from ip4_130;
insert into ipAll select (131*256*256) + (b*256) + c as ipkey, country, city from ip4_131;
insert into ipAll select (132*256*256) + (b*256) + c as ipkey, country, city from ip4_132;
insert into ipAll select (133*256*256) + (b*256) + c as ipkey, country, city from ip4_133;
insert into ipAll select (134*256*256) + (b*256) + c as ipkey, country, city from ip4_134;
insert into ipAll select (135*256*256) + (b*256) + c as ipkey, country, city from ip4_135;
insert into ipAll select (136*256*256) + (b*256) + c as ipkey, country, city from ip4_136;
insert into ipAll select (137*256*256) + (b*256) + c as ipkey, country, city from ip4_137;
insert into ipAll select (138*256*256) + (b*256) + c as ipkey, country, city from ip4_138;
insert into ipAll select (139*256*256) + (b*256) + c as ipkey, country, city from ip4_139;
insert into ipAll select (140*256*256) + (b*256) + c as ipkey, country, city from ip4_140;
insert into ipAll select (141*256*256) + (b*256) + c as ipkey, country, city from ip4_141;
insert into ipAll select (142*256*256) + (b*256) + c as ipkey, country, city from ip4_142;
insert into ipAll select (143*256*256) + (b*256) + c as ipkey, country, city from ip4_143;
insert into ipAll select (144*256*256) + (b*256) + c as ipkey, country, city from ip4_144;
insert into ipAll select (145*256*256) + (b*256) + c as ipkey, country, city from ip4_145;
insert into ipAll select (146*256*256) + (b*256) + c as ipkey, country, city from ip4_146;
insert into ipAll select (147*256*256) + (b*256) + c as ipkey, country, city from ip4_147;
insert into ipAll select (148*256*256) + (b*256) + c as ipkey, country, city from ip4_148;
insert into ipAll select (149*256*256) + (b*256) + c as ipkey, country, city from ip4_149;
insert into ipAll select (150*256*256) + (b*256) + c as ipkey, country, city from ip4_150;
insert into ipAll select (151*256*256) + (b*256) + c as ipkey, country, city from ip4_151;
insert into ipAll select (152*256*256) + (b*256) + c as ipkey, country, city from ip4_152;
insert into ipAll select (153*256*256) + (b*256) + c as ipkey, country, city from ip4_153;
insert into ipAll select (154*256*256) + (b*256) + c as ipkey, country, city from ip4_154;
insert into ipAll select (155*256*256) + (b*256) + c as ipkey, country, city from ip4_155;
insert into ipAll select (156*256*256) + (b*256) + c as ipkey, country, city from ip4_156;
insert into ipAll select (157*256*256) + (b*256) + c as ipkey, country, city from ip4_157;
insert into ipAll select (158*256*256) + (b*256) + c as ipkey, country, city from ip4_158;
insert into ipAll select (159*256*256) + (b*256) + c as ipkey, country, city from ip4_159;
insert into ipAll select (160*256*256) + (b*256) + c as ipkey, country, city from ip4_160;
insert into ipAll select (161*256*256) + (b*256) + c as ipkey, country, city from ip4_161;
insert into ipAll select (162*256*256) + (b*256) + c as ipkey, country, city from ip4_162;
insert into ipAll select (163*256*256) + (b*256) + c as ipkey, country, city from ip4_163;
insert into ipAll select (164*256*256) + (b*256) + c as ipkey, country, city from ip4_164;
insert into ipAll select (165*256*256) + (b*256) + c as ipkey, country, city from ip4_165;
insert into ipAll select (166*256*256) + (b*256) + c as ipkey, country, city from ip4_166;
insert into ipAll select (167*256*256) + (b*256) + c as ipkey, country, city from ip4_167;
insert into ipAll select (168*256*256) + (b*256) + c as ipkey, country, city from ip4_168;
insert into ipAll select (169*256*256) + (b*256) + c as ipkey, country, city from ip4_169;
insert into ipAll select (170*256*256) + (b*256) + c as ipkey, country, city from ip4_170;
insert into ipAll select (171*256*256) + (b*256) + c as ipkey, country, city from ip4_171;
insert into ipAll select (172*256*256) + (b*256) + c as ipkey, country, city from ip4_172;
insert into ipAll select (173*256*256) + (b*256) + c as ipkey, country, city from ip4_173;
insert into ipAll select (174*256*256) + (b*256) + c as ipkey, country, city from ip4_174;
insert into ipAll select (175*256*256) + (b*256) + c as ipkey, country, city from ip4_175;
insert into ipAll select (176*256*256) + (b*256) + c as ipkey, country, city from ip4_176;
insert into ipAll select (177*256*256) + (b*256) + c as ipkey, country, city from ip4_177;
insert into ipAll select (178*256*256) + (b*256) + c as ipkey, country, city from ip4_178;
insert into ipAll select (179*256*256) + (b*256) + c as ipkey, country, city from ip4_179;
insert into ipAll select (180*256*256) + (b*256) + c as ipkey, country, city from ip4_180;
insert into ipAll select (181*256*256) + (b*256) + c as ipkey, country, city from ip4_181;
insert into ipAll select (182*256*256) + (b*256) + c as ipkey, country, city from ip4_182;
insert into ipAll select (183*256*256) + (b*256) + c as ipkey, country, city from ip4_183;
insert into ipAll select (184*256*256) + (b*256) + c as ipkey, country, city from ip4_184;
insert into ipAll select (185*256*256) + (b*256) + c as ipkey, country, city from ip4_185;
insert into ipAll select (186*256*256) + (b*256) + c as ipkey, country, city from ip4_186;
insert into ipAll select (187*256*256) + (b*256) + c as ipkey, country, city from ip4_187;
insert into ipAll select (188*256*256) + (b*256) + c as ipkey, country, city from ip4_188;
insert into ipAll select (189*256*256) + (b*256) + c as ipkey, country, city from ip4_189;
insert into ipAll select (190*256*256) + (b*256) + c as ipkey, country, city from ip4_190;
insert into ipAll select (191*256*256) + (b*256) + c as ipkey, country, city from ip4_191;
insert into ipAll select (192*256*256) + (b*256) + c as ipkey, country, city from ip4_192;
insert into ipAll select (193*256*256) + (b*256) + c as ipkey, country, city from ip4_193;
insert into ipAll select (194*256*256) + (b*256) + c as ipkey, country, city from ip4_194;
insert into ipAll select (195*256*256) + (b*256) + c as ipkey, country, city from ip4_195;
insert into ipAll select (196*256*256) + (b*256) + c as ipkey, country, city from ip4_196;
insert into ipAll select (197*256*256) + (b*256) + c as ipkey, country, city from ip4_197;
insert into ipAll select (198*256*256) + (b*256) + c as ipkey, country, city from ip4_198;
insert into ipAll select (199*256*256) + (b*256) + c as ipkey, country, city from ip4_199;
insert into ipAll select (200*256*256) + (b*256) + c as ipkey, country, city from ip4_200;
insert into ipAll select (201*256*256) + (b*256) + c as ipkey, country, city from ip4_201;
insert into ipAll select (202*256*256) + (b*256) + c as ipkey, country, city from ip4_202;
insert into ipAll select (203*256*256) + (b*256) + c as ipkey, country, city from ip4_203;
insert into ipAll select (204*256*256) + (b*256) + c as ipkey, country, city from ip4_204;
insert into ipAll select (205*256*256) + (b*256) + c as ipkey, country, city from ip4_205;
insert into ipAll select (206*256*256) + (b*256) + c as ipkey, country, city from ip4_206;
insert into ipAll select (207*256*256) + (b*256) + c as ipkey, country, city from ip4_207;
insert into ipAll select (208*256*256) + (b*256) + c as ipkey, country, city from ip4_208;
insert into ipAll select (209*256*256) + (b*256) + c as ipkey, country, city from ip4_209;
insert into ipAll select (210*256*256) + (b*256) + c as ipkey, country, city from ip4_210;
insert into ipAll select (211*256*256) + (b*256) + c as ipkey, country, city from ip4_211;
insert into ipAll select (212*256*256) + (b*256) + c as ipkey, country, city from ip4_212;
insert into ipAll select (213*256*256) + (b*256) + c as ipkey, country, city from ip4_213;
insert into ipAll select (214*256*256) + (b*256) + c as ipkey, country, city from ip4_214;
insert into ipAll select (215*256*256) + (b*256) + c as ipkey, country, city from ip4_215;
insert into ipAll select (216*256*256) + (b*256) + c as ipkey, country, city from ip4_216;
insert into ipAll select (217*256*256) + (b*256) + c as ipkey, country, city from ip4_217;
insert into ipAll select (218*256*256) + (b*256) + c as ipkey, country, city from ip4_218;
insert into ipAll select (219*256*256) + (b*256) + c as ipkey, country, city from ip4_219;
insert into ipAll select (220*256*256) + (b*256) + c as ipkey, country, city from ip4_220;
insert into ipAll select (221*256*256) + (b*256) + c as ipkey, country, city from ip4_221;
insert into ipAll select (222*256*256) + (b*256) + c as ipkey, country, city from ip4_222;
insert into ipAll select (223*256*256) + (b*256) + c as ipkey, country, city from ip4_223;
insert into ipAll select (224*256*256) + (b*256) + c as ipkey, country, city from ip4_224;
insert into ipAll select (225*256*256) + (b*256) + c as ipkey, country, city from ip4_225;
insert into ipAll select (226*256*256) + (b*256) + c as ipkey, country, city from ip4_226;
insert into ipAll select (227*256*256) + (b*256) + c as ipkey, country, city from ip4_227;
insert into ipAll select (228*256*256) + (b*256) + c as ipkey, country, city from ip4_228;
insert into ipAll select (229*256*256) + (b*256) + c as ipkey, country, city from ip4_229;
insert into ipAll select (230*256*256) + (b*256) + c as ipkey, country, city from ip4_230;
insert into ipAll select (231*256*256) + (b*256) + c as ipkey, country, city from ip4_231;
insert into ipAll select (232*256*256) + (b*256) + c as ipkey, country, city from ip4_232;
insert into ipAll select (233*256*256) + (b*256) + c as ipkey, country, city from ip4_233;
insert into ipAll select (234*256*256) + (b*256) + c as ipkey, country, city from ip4_234;
insert into ipAll select (235*256*256) + (b*256) + c as ipkey, country, city from ip4_235;
insert into ipAll select (236*256*256) + (b*256) + c as ipkey, country, city from ip4_236;
insert into ipAll select (237*256*256) + (b*256) + c as ipkey, country, city from ip4_237;
insert into ipAll select (238*256*256) + (b*256) + c as ipkey, country, city from ip4_238;
insert into ipAll select (239*256*256) + (b*256) + c as ipkey, country, city from ip4_239;
insert into ipAll select (240*256*256) + (b*256) + c as ipkey, country, city from ip4_240;
insert into ipAll select (241*256*256) + (b*256) + c as ipkey, country, city from ip4_241;
insert into ipAll select (242*256*256) + (b*256) + c as ipkey, country, city from ip4_242;
insert into ipAll select (243*256*256) + (b*256) + c as ipkey, country, city from ip4_243;
insert into ipAll select (244*256*256) + (b*256) + c as ipkey, country, city from ip4_244;
insert into ipAll select (245*256*256) + (b*256) + c as ipkey, country, city from ip4_245;
insert into ipAll select (246*256*256) + (b*256) + c as ipkey, country, city from ip4_246;
insert into ipAll select (247*256*256) + (b*256) + c as ipkey, country, city from ip4_247;
insert into ipAll select (248*256*256) + (b*256) + c as ipkey, country, city from ip4_248;
insert into ipAll select (249*256*256) + (b*256) + c as ipkey, country, city from ip4_249;
insert into ipAll select (250*256*256) + (b*256) + c as ipkey, country, city from ip4_250;
insert into ipAll select (251*256*256) + (b*256) + c as ipkey, country, city from ip4_251;
insert into ipAll select (252*256*256) + (b*256) + c as ipkey, country, city from ip4_252;
insert into ipAll select (253*256*256) + (b*256) + c as ipkey, country, city from ip4_253;
insert into ipAll select (254*256*256) + (b*256) + c as ipkey, country, city from ip4_254;
insert into ipAll select (255*256*256) + (b*256) + c as ipkey, country, city from ip4_255;


Then in your lookup code calculate ipkey as:
(A * 256 * 256) + (B * 256) + C

I'm curious how that will perform... please let us know.

Thanks,
Rob

[malo]

Has anybody ever tried to run the DB on postgreSQL and did speed comparisons??

I am planning to use it on PostgreSQL but store all 256 tables into one large tables and putting my index on a seperated disk.

Also, I want to see if the IP datatype in postgresql can be of any help speeding up the search process.


Any ideas, thoughts etc?

cheers,
Ries

There's actually a nice format you can use to store it in if you have one long table - it's called an IP Number

I think mysql had functions built in to convert the two (again, I think) - not sure about postgresql though. The IP number is basically a dword which is converted from the IP address quadrants.

Code: Select all

216.177.121.11 = 3635509515
((216*256 + 177) * 256 + 121) * 256 + 11 = 3635509515


This is a nice discussion on the ways you can represent a dotted quad:
http://lists.sans.org/pipermail/list/2003-August/058208.html

Basically, using this idea you would make that your primary, clustered key and end up with another fast way to get at the data, knowing the ip address. Then if you provided that across from the cityid, index the cityid and you've got a fast way to do the reverse.

so far, so good. But I have only one value. Could anyone give a example for a select statement please ?

This is not possible:
mysql> select count(*) from ip4 where ip > '3635509515';
+----------+
| count(*) |
+----------+
| 402780 |
+----------+
1 row in set (0.37 sec)

mysql>
---

ip4 table looks like:
mysql> desc ip4;
+---------+----------------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+-------------------+-------+
| ip | int(10) unsigned | | MUL | 0 | |
| country | smallint(5) unsigned | | MUL | 0 | |
| city | smallint(5) unsigned | | MUL | 0 | |
| cron | timestamp | YES | MUL | CURRENT_TIMESTAMP | |
+---------+----------------------+------+-----+-------------------+-------+
4 rows in set (0.00 sec)
---

if we would use a range, then it would be easy to do a select like:
mysql> select * from ip4 where ip > 3635509515 and ip < '3635510000';
+------------+---------+------+---------------------+
| ip | country | city | cron |
+------------+---------+------+---------------------+
| 3635509760 | 226 | 191 | 2005-03-12 18:05:03 |
+------------+---------+------+---------------------+
1 row in set (0.00 sec)

mysql>

and then:
mysql> select * from cityByCountry where city = '191' and country = '226';
+------+---------+-----------------+---------+---------+----------+
| city | country | name | lat | lng | state |
+------+---------+-----------------+---------+---------+----------+
| 191 | 226 | Chicago%2C%20IL | 41.8371 | -87.685 | Illinois |
+------+---------+-----------------+---------+---------+----------+
1 row in set (0.05 sec)

mysql>
---

right ?

cheers,
martin

[robocoder]

Assuming these are still /24 blocks, the end of the range is implicitly ip+255. So, try something like:

select * from ip4 where ip <= 3635509515 and ip+255 >= 3635509515;

[malo]

Assuming these are still /24 blocks, the end of the range is implicitly ip+255. So, try something like:

select * from ip4 where ip <= 3635509515 and ip+255 >= 3635509515;

well, right Assuming this, but you never know
I guess the only chance woulbe to have the block as a "form" - "to" information inside the database. Any chance to generate this information ?

[robocoder]

I would wait until Brad releases the new database structure alluded to here.

In the meantime, you could try something like:

alter table ip4 add (endip cidr);
update ip4 set endip=ip+255;

The query would then change to:

select * from ip4 where ip <= 3635509515 and endip >= 3635509515;