Great article!

I had downloaded the following scripts for a very similar database schema, that speeds up "select" queries against the blocks table. It increased my "lookups per second" from around 45 to 3500, really.

I dropped indexes on GeoLiteCity_blocks table and created the following two indexes:

CREATE NONCLUSTERED INDEX [NCL_END_IP] ON [dbo].[GeoLiteCity_blocks]

(

[endIpnum] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [NCL_IND_STARTIP] ON [dbo].[GeoLiteCity_blocks]

(

[startIpnum] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

Then to find the LocationID or any other column for a specific IP number is as follows:

declare @IP bigint

set @IP=123456789

select * from GeoLiteCity_blocks

where startIpnum=(select max(startIpnum) from GeoLiteCity_blocks where startIpnum<=@IP)

and endIpnum >=@IP

Please check this on a development environment with backups and tell me the results. Any comparisons would be great actually, I never had the time to do that.

By the way, I could not find the original source of this trick. If anyone knows, you may add it also.