Table Design for Optimum Speed ~1M Rows

I've been tasked to start porting our existing (and bloated) excel sheet with all our company wide IP information into a MySQL database. I've just converted several other excel sheets, but none held more then a thousand rows. Because this is a new database and because it will hold close to if not more than 1 million records, I want to make sure I design it optimally from the get go.

The information we nee开发者_开发知识库d to have for each address:

• IP Address
• Mask
• Hostname
• Department
• Division
• Network (Voice, Data, Routing, etc.)

Any help would be greatly appreciated!

Thanks :)

A few things to take into consideration

• When you ask for optimum speed, would that be speed of inserts or speed of selects. Both can be solved but at the expense of its counterpart.
• 1 million rows really isn't that much for a well designed database.

I would advise you to normalize your data into something like the following

CREATE TABLE Hosts (
  HostID AUTOINCREMENT PRIMARY KEY
  , IPAddress  VARCHAR(15)
  , Mask     VARCHAR(15)
  , Hostname VARCHAR(32)
)

CREATE TABLE Departments (
  DepartmentID AUTOINCREMENT PRIMARY KEY
  , Department VARCHAR(32)
)

CREATE TABLE Divisions (
  DivisionID AUTOINCREMENT PRIMARY KEY
  , Division VARCHAR(32)
)

CREATE TABLE Networks (
  NetworkID AUTOINCREMENT PRIMARY KEY
  , Network VARCHAR(32)
)  

CREATE TABLE CompanyWide (
  HostID INTEGER
  , DepartmentID INTEGER
  , DivisionID INTEGER
  , NetworkID INTEGER

  , FOREIGN KEY (HostID) REFERENCES Hosts(HostID)
  , FOREIGN KEY (Department) REFERENCES Hosts(DepartmentID)
  , FOREIGN KEY (DivisionID) REFERENCES Hosts(DivisionID)
  , FOREIGN KEY (NetworkID) REFERENCES Hosts(NetworkID)
)