Crimson: Added SQL indexes

go/src/infra/crimson/sql/schema.sql

Index: go/src/infra/crimson/sql/schema.sql
diff --git a/go/src/infra/crimson/sql/schema.sql b/go/src/infra/crimson/sql/schema.sql
index 83474c0b6770e5a60386f460499603b20fadc541..d76dcdbb3cb5bf419ce5ca8ee9e81c75aac03027 100644
--- a/go/src/infra/crimson/sql/schema.sql
+++ b/go/src/infra/crimson/sql/schema.sql
@@ -13,6 +13,9 @@ CREATE TABLE ip_range (
   end_ip varchar(34) NOT NULL
 ) ENGINE=INNODB;
 
+CREATE index ip_range_start_ip_idx ON ip_range(start_ip);

[sheyang, 2016/07/01 21:55:26]

The purpose of index start_ip and end_ip is for querying with where clause I
guess. Would storing ip as varchar still work? And how about ipv6?

[pgervais, 2016/07/01 22:26:36]

That's correct. 
IP and mac addresses are stored as hexadecimal strings, that's why it's 34
characters long (IPv6: 16bytes*2 + '0x' prefix).
With this representation, lexicographical order sorts IPv6 and IPv4 addresses in
two separate blocks.

+CREATE index ip_ragne_start_ip_idx ON ip_range(end_ip);
+
 CREATE TABLE host (
   site varchar(20) NOT NULL,

[sheyang, 2016/07/01 21:55:26]

Is this a FK?

[pgervais, 2016/07/01 22:26:36]

It looks like a foreign key but it's not. There is no SQL-friendly way to join
the two tables. An IP address corresponds to an IP range but it's not a join...

   hostname varchar(63) NOT NULL,
@@ -21,3 +24,6 @@ CREATE TABLE host (
   boot_class varchar(20),
   PRIMARY KEY (mac_addr)
 ) ENGINE=INNODB;
+
+CREATE index host_site_hostname_idx ON host(site, hostname);
+CREATE index host_site_ip_idx ON host(site, ip);