On Mon 23 Jul 2007, Phil Mayers wrote:
On Mon, 2007-07-23 at 10:11 -0500, Hugh Messenger wrote:
While we are on the subject of schemas (schemi?) ...
Would it be worth adding some indexing to the basic sqlippool table? At the moment, only the 'id' is indexed, by virtue of being the primary key.
I should imagine that even a medium sized provider could end up with quite large radippool tables, at a thousand or so entries for every four class C's. I'm just a corner shop ISP, but even I'll end up with about 10k entries by the time I'm done migrating everything from Funk.
With 3 queries per login, 1 each for every stop, start and update, plus whatever work the backend provisioning is doing to check and update the pools, that's a lot of extra work for the db with no indexing.
Also, unless there is some bizarre circumstance I haven't thought of where the same IP would appear twice, make FrameIPAddress a 'unique' index, to prevent accidentally adding the same IP twice when provisioning the table.
VRFs with overlapping address space are one possible use I can think of; handing out special IPs which redirect to "you are banned" or null route is another.
Don't make it unique. Do index it.
I disagree. It DOES need to be unique and indexed. In the case where you are virtualising things with VRF (Which I do in production) you need to add an extra VRF key to the queries (In my case I use calledstationid) and you would adjust the index/constraints accordingly. -- Peter Nixon http://peternixon.net/