2.0 mysql.sql

[Peter Nixon]

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/