2.0 mysql.sql

[Phil Mayers]

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.