CREATE TABLE cell_templates (
  template_idx bigint(20) unsigned NOT NULL,
  PRIMARY KEY (template_idx)
);


INSERT INTO cell_templates VALUES
(223102176), (222689488), (222113056), (224465120),
(222676832), (222692048), (224474640), (224752944),
(222869504), (224752945); 

CREATE TABLE cells (
  cell_idx int(10) unsigned NOT NULL,
  owner_lib_temp_idx int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (cell_idx)
);


INSERT INTO cells VALUES 
(0, 224752945), (1, 222113056), (2, 223102176), (3, 222676832),
(4, 222113056), (5, 222689488), (6, 222692048), (7, 222676832),
(8, 224465120), (9, 224474640), (10,222676832), (11,224474640),
(12,224474640), (13,224474640), (14,224474640), (15,224465120),
(16,224752944), (17,224752944), (18,224752944), (19,NULL);


CREATE TABLE checks (
  check_idx int(10) unsigned NOT NULL,
  check_value float DEFAULT NULL,
  check_state enum('h','l') NOT NULL,
  mes_node_idx int(10) unsigned NOT NULL,
  ref_tran enum('h2l','l2h','h2z','z2h','l2z','z2l') NOT NULL,
  ref_node_idx int(10) unsigned NOT NULL,
  check_types text,
  clk_idx int(10) unsigned DEFAULT NULL,
  clk_delay float DEFAULT NULL,
  clk_slope float DEFAULT NULL,
  clk_tran enum('h2l','l2h','h2z','z2h','l2z','z2l') NOT NULL,
  clk_pin_idx int(10) unsigned DEFAULT NULL,
  imp_clk_idx int(10) unsigned DEFAULT NULL,
  imp_clk_trans enum('f','r') DEFAULT NULL,
  PRIMARY KEY (check_idx),
  KEY clk_idx (clk_idx),
  KEY clk_pin_idx (clk_pin_idx),
  KEY FK_CHECKS_IMP_CLK_IDX (imp_clk_idx),
  KEY FK_CHECKS_MES_NODE_IDX (mes_node_idx),
  KEY FK_CHECKS_REF_NODE_IDX (ref_node_idx)
);


INSERT INTO checks VALUES (16,0.725,'h',11,'l2h',64,'setup evr',1,0,0,'l2h',71,1,'f'),(17,0.86,'l',11,'l2h',64,'setup evr',1,0,0,'l2h',71,1,'f'),(18,0.344,'h',11,'l2h',64,'hold evr',1,0,0,'l2h',71,NULL,NULL),(19,0.245,'l',11,'l2h',64,'hold evr',1,0,0,'l2h',71,NULL,NULL),(12,0.725,'h',12,'l2h',64,'setup evr',1,0,0,'l2h',71,1,'f'),(13,0.86,'l',12,'l2h',64,'setup evr',1,0,0,'l2h',71,1,'f'),(14,0.344,'h',12,'l2h',64,'hold evr',1,0,0,'l2h',71,NULL,NULL),(15,0.245,'l',12,'l2h',64,'hold evr',1,0,0,'l2h',71,NULL,NULL),(8,0.725,'h',13,'l2h',64,'setup evr',1,0,0,'l2h',71,1,'f'),(9,0.86,'l',13,'l2h',64,'setup evr',1,0,0,'l2h',71,1,'f'),(10,0.344,'h',13,'l2h',64,'hold evr',1,0,0,'l2h',71,NULL,NULL),(11,0.245,'l',13,'l2h',64,'hold evr',1,0,0,'l2h',71,NULL,NULL),(4,0.725,'h',14,'l2h',64,'setup evr',1,0,0,'l2h',71,1,'f'),(5,0.86,'l',14,'l2h',64,'setup evr',1,0,0,'l2h',71,1,'f'),(6,0.344,'h',14,'l2h',64,'hold evr',1,0,0,'l2h',71,NULL,NULL),(7,0.245,'l',14,'l2h',64,'hold evr',1,0,0,'l2h',71,NULL,NULL),(0,0.125,'h',15,'h2l',64,'setup evr',1,0,0,'h2l',71,1,'r'),(1,0.26,'l',15,'h2l',64,'setup evr',1,0,0,'h2l',71,1,'r'),(2,0.344,'h',15,'h2l',64,'hold evr',1,0,0,'h2l',71,NULL,NULL),(3,0.245,'l',15,'h2l',64,'hold evr',1,0,0,'h2l',71,NULL,NULL),(20,0.0118898,'h',20,'h2l',21,'hold aux4 latch',1,0.070985,0.0262052,'h2l',21,NULL,NULL),(23,0.101109,'h',20,'h2l',21,'setup aux4 latch',1,0.0706484,0.0240379,'h2l',21,1,'r'),(26,0.0334866,'l',20,'h2l',21,'hold aux4 latch',1,0.070985,0.0262052,'h2l',21,NULL,NULL),(29,0.138044,'l',20,'h2l',21,'setup aux4 latch',1,0.0706484,0.0240379,'h2l',21,1,'r'),(32,0.263856,'h',31,'l2h',32,'hold aux4 master_slave',1,0.0687226,0.0277259,'l2h',32,NULL,NULL),(33,0.105783,'h',31,'l2h',32,'setup aux4 master_slave',1,0.0684121,0.0252103,'l2h',32,1,'f'),(34,0.23693,'l',31,'l2h',32,'hold aux4 master_slave',1,0.0687226,0.0277259,'l2h',32,NULL,NULL),(35,0.0194297,'l',31,'l2h',32,'setup aux4 master_slave',1,0.0684121,0.0252103,'l2h',32,1,'f'),(22,0.0384897,'h',34,'h2l',35,'hold aux4 latch',1,0.0334743,0.0297444,'l2h',35,NULL,NULL),(25,0.136617,'h',34,'h2l',35,'setup aux4 latch',1,0.0329424,0.0288748,'l2h',35,1,'f'),(28,0.107186,'l',34,'h2l',35,'hold aux4 latch',1,0.0334743,0.0297444,'l2h',35,NULL,NULL),(31,0.151837,'l',34,'h2l',35,'setup aux4 latch',1,0.0329424,0.0288748,'l2h',35,1,'f'),(21,0.0384636,'h',42,'h2l',43,'hold aux4 latch',1,0.0264588,0.0298935,'l2h',43,NULL,NULL),(24,0.135623,'h',42,'h2l',43,'setup aux4 latch',1,0.026646,0.0295023,'l2h',43,1,'f'),(27,0.107139,'l',42,'h2l',43,'hold aux4 latch',1,0.0264588,0.0298935,'l2h',43,NULL,NULL),(30,0.155883,'l',42,'h2l',43,'setup aux4 latch',1,0.026646,0.0295023,'l2h',43,1,'f'),(36,0,'l',45,'l2h',46,'setup implicit gated_clock',1,-0.00425521,0.0957448,'l2h',46,1,'f'),(37,0,'h',45,'h2l',46,'hold implicit gated_clock',1,-0.00510011,0.0948999,'h2l',46,NULL,NULL),(38,0,'h',45,'l2h',46,'setup implicit gated_clock',1,-0.00425521,0.0957448,'l2h',46,1,'f'),(39,0,'l',45,'h2l',46,'hold implicit gated_clock',1,-0.00510011,0.0948999,'h2l',46,NULL,NULL),(40,-0.0997728,'l',56,'h2l',57,'hold aux4 tri_state',1,-0.00510011,0.0948999,'h2l',60,NULL,NULL),(42,0.466831,'l',56,'h2l',57,'setup aux4 tri_state',1,-0.00384036,0.0961596,'h2l',60,1,'r'),(44,-0.0289962,'l',56,'h2l',57,'hold aux4 tri_state',1,-0.00510011,0.0948999,'h2l',60,NULL,NULL),(46,0.449079,'l',56,'h2l',57,'setup aux4 tri_state',1,-0.00384036,0.0961596,'h2l',60,1,'r'),(48,0.0965598,'l',56,'l2h',57,'setup aux4 tri_state',1,-0.00425521,0.0957448,'l2h',60,1,'f'),(50,0.0441869,'l',56,'l2h',57,'setup aux4 tri_state',1,-0.00425521,0.0957448,'l2h',60,1,'f'),(52,-0.0823508,'h',56,'l2h',57,'setup aux4 tri_state',1,-0.00425521,0.0957448,'l2h',60,1,'f'),(54,-0.175537,'h',56,'l2h',57,'setup aux4 tri_state',1,-0.00425521,0.0957448,'l2h',60,1,'f'),(56,-0.216725,'h',56,'h2l',57,'hold aux4 tri_state',1,-0.00510011,0.0948999,'h2l',60,NULL,NULL),(58,-0.169621,'h',56,'h2l',57,'hold aux4 tri_state',1,-0.00510011,0.0948999,'h2l',60,NULL,NULL),(60,-0.0629527,'h',58,'h2l',57,'hold aux4 tri_state',1,-0.00510011,0.0948999,'h2l',60,NULL,NULL),(62,0.376311,'h',58,'h2l',57,'setup aux4 tri_state',1,-0.00384036,0.0961596,'h2l',60,1,'r'),(64,-0.0231595,'l',58,'h2l',57,'hold aux4 tri_state',1,-0.00510011,0.0948999,'h2l',60,NULL,NULL),(66,0.404595,'l',58,'h2l',57,'setup aux4 tri_state',1,-0.00384036,0.0961596,'h2l',60,1,'r'),(41,-0.0740438,'l',60,'h2l',61,'hold aux4 tri_state',1,-0.00510011,0.0948999,'h2l',64,NULL,NULL),(43,0.330969,'l',60,'h2l',61,'setup aux4 tri_state',1,-0.00384036,0.0961596,'h2l',64,1,'r'),(45,0.00190912,'l',60,'h2l',61,'hold aux4 tri_state',1,-0.00510011,0.0948999,'h2l',64,NULL,NULL),(47,0.289028,'l',60,'h2l',61,'setup aux4 tri_state',1,-0.00384036,0.0961596,'h2l',64,1,'r'),(49,0.0995147,'l',60,'l2h',61,'setup aux4 tri_state',1,-0.00425521,0.0957448,'l2h',64,1,'f'),(51,0.0435754,'l',60,'l2h',61,'setup aux4 tri_state',1,-0.00425521,0.0957448,'l2h',64,1,'f'),(53,-0.0585267,'h',60,'l2h',61,'setup aux4 tri_state',1,-0.00425521,0.0957448,'l2h',64,1,'f'),(55,-0.138209,'h',60,'l2h',61,'setup aux4 tri_state',1,-0.00425521,0.0957448,'l2h',64,1,'f'),(57,-0.191363,'h',60,'h2l',61,'hold aux4 tri_state',1,-0.00510011,0.0948999,'h2l',64,NULL,NULL),(59,-0.138857,'h',60,'h2l',61,'hold aux4 tri_state',1,-0.00510011,0.0948999,'h2l',64,NULL,NULL),(61,-0.0383603,'h',62,'h2l',61,'hold aux4 tri_state',1,-0.00510011,0.0948999,'h2l',64,NULL,NULL),(63,0.239179,'h',62,'h2l',61,'setup aux4 tri_state',1,-0.00384036,0.0961596,'h2l',64,1,'r'),(65,0.0105287,'l',62,'h2l',61,'hold aux4 tri_state',1,-0.00510011,0.0948999,'h2l',64,NULL,NULL),(67,0.24999,'l',62,'h2l',61,'setup aux4 tri_state',1,-0.00384036,0.0961596,'h2l',64,1,'r');


CREATE TABLE clocks (
  clock_idx int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (clock_idx)
);


INSERT INTO clocks VALUES (1),(2);


CREATE TABLE nets (
  net_idx int(10) unsigned NOT NULL,
  PRIMARY KEY (net_idx)
);


INSERT INTO nets VALUES 
(0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19),
(20), (21), (22), (23), (24), (25), (26), (27), (28);


CREATE TABLE path_segments (
  seg_idx int(10) unsigned NOT NULL AUTO_INCREMENT,
  event_reference_clock_idx int(10) unsigned NOT NULL,
  PRIMARY KEY (seg_idx),
  KEY event_reference_clock_idx (event_reference_clock_idx)
);


INSERT INTO path_segments VALUES
(1,1), (2,1), (3,1), (4,1), (5,1), (6,1), (7,1), (8,1), (9,1),
(10,1), (11,1), (12,1), (13,1), (14,1), (15,1), (16,1), (17,1), (18,1), (19,1),
(20,1), (21,1), (22,1), (23,1), (24,1), (25,1), (26,1), (27,1), (28,1), (29,1),
(30,1), (31,1), (32,1), (33,1), (34,1), (35,1), (36,1), (37,1), (38,1), (39,1),
(40,1), (41,1), (42,1), (43,1), (44,1), (45,1), (46,1), (47,1), (48,1), (49,1),
(50,1), (51,1), (52,2), (53,2), (54,2), (55,2), (56,1), (57,1), (58,1), (59,1),
(60,1), (61,2), (62,2), (63,2), (64,2), (65,1), (66,1), (67,1), (68,1), (69,1),
(70,2), (71,2), (72,2), (73,2), (74,1), (75,1), (76,1), (77,1), (78,1), (79,2),
(80,2), (81,2), (82,2), (83,1), (84,1), (85,1), (86,1), (87,1), (88,1), (89,1),
(90,1), (91,1), (92,1), (93,1), (94,1), (95,1), (96,1), (97,1), (98,1), (99,1);


CREATE TABLE paths (
path_idx int(10) unsigned NOT NULL,
check_idx int(10) unsigned NOT NULL,
last_clocked_seg_idx int(10) unsigned DEFAULT NULL,
last_clocked_pin_idx int(10) unsigned DEFAULT NULL,
last_clocked_pin_net_idx int(10) unsigned DEFAULT NULL,
end_seg_idx int(10) unsigned NOT NULL,
end_pin_idx int(10) unsigned NOT NULL,
end_pin_net_idx int(10) unsigned NOT NULL,
PRIMARY KEY (path_idx),
KEY check_idx (check_idx),
KEY last_clocked_seg_idx (last_clocked_seg_idx),
KEY last_clocked_pin_idx (last_clocked_pin_idx),
KEY last_clocked_pin_net_idx (last_clocked_pin_net_idx),
KEY end_seg_idx (end_seg_idx),
KEY end_pin_idx (end_pin_idx),
KEY end_pin_net_idx (end_pin_net_idx)
);


INSERT INTO paths VALUES
(23,8,4,35,13,6,13,14),
(22,29,NULL,NULL,NULL,10,20,1),
(14,8,NULL,NULL,NULL,16,13,14),
(17,1,NULL,NULL,NULL,20,15,10),
(21,12,23,43,16,25,12,17),
(11,1,NULL,NULL,NULL,33,15,10),
(13,62,NULL,NULL,NULL,36,61,21),
(10,9,NULL,NULL,NULL,42,13,14),
(16,9,4,35,13,44,13,14),
(18,4,NULL,NULL,NULL,48,14,5),
(20,5,NULL,NULL,NULL,51,14,5),
(2,13,NULL,NULL,NULL,57,12,17),
(6,66,NULL,NULL,NULL,60,61,21),
(5,12,NULL,NULL,NULL,66,12,17),
(12,66,NULL,NULL,NULL,69,61,21),
(1,13,NULL,NULL,NULL,75,12,17),
(7,0,NULL,NULL,NULL,78,15,10),
(4,12,NULL,NULL,NULL,84,12,17),
(15,13,23,43,16,86,12,17),
(3,1,NULL,NULL,NULL,89,15,10),
(9,62,NULL,NULL,NULL,92,61,21),
(8,0,NULL,NULL,NULL,95,15,10),
(19,0,NULL,NULL,NULL,99,15,10);


CREATE TABLE pins (
  pin_idx int(10) unsigned NOT NULL,
  owner_cell_idx int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (pin_idx),
  KEY FK_PINS_OWNER_CELL_IDX (owner_cell_idx)
);

CREATE TABLE timing_nodes (
  node_idx int(10) unsigned NOT NULL,
  net_idx int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (node_idx),
  KEY net_idx (net_idx)
);

INSERT INTO timing_nodes VALUES
(0,25), (1,23), (2,20), (3,19), (4,18), (5,9), (6,11), (7,8), (8,6), (9,0),
(10,2), (11,24), (12,17), (13,14), (14,5), (15,10), (16,0), (17,1), (18,2), (19,3),
(20,1), (21,3), (22,4), (23,4), (24,5), (25,9), (26,8), (27,7), (28,4), (29,6),
(30,10), (31,11), (32,3), (33,7), (34,12), (35,13), (36,14), (37,2), (38,13), (39,6),
(40,0), (41,12), (42,15), (43,16), (44,17), (45,18), (46,2), (47,16), (48,20), (49,19),
(50,15), (51,6), (52,0), (53,21), (54,8), (55,22), (56,23), (57,2), (58,21), (59,24),
(60,25), (61,2), (62,22), (63,24), (64,28), (65,28), (66,28);


INSERT INTO pins VALUES
(0,25), (1,0), (2,0), (3,0), (4,0), (5,0), (6,0), (7,0), (8,0), (9,0),
(10,0), (11,0), (12,0), (13,0), (14,0), (15,0), (16,1), (17,1), (18,2), (19,2),
(20,3), (21,3), (22,3), (23,4), (24,4), (25,5), (26,5), (27,5), (28,5), (29,5),
(30,5), (31,6), (32,6), (33,6), (34,7), (35,7), (36,7), (37,8), (38,8), (39,9),
(40,9), (41,9), (42,10), (43,10), (44,10), (45,11), (46,11), (47,11), (48,12), (49,12),
(50,12), (51,13), (52,13), (53,13), (54,14), (55,14), (56,14), (57,15), (58,15), (59,16),
(60,16), (61,16), (62,16), (63,17), (64,17), (65,17), (66,17), (67,18), (68,18), (69,18),
(70,18),
(71,NULL);

EXPLAIN SELECT * FROM
   paths,
   checks,
   timing_nodes,
   pins as p1, pins as p2,
   nets as n1, nets as n2, nets as n3,
   cells as c1, cells as c2,
   path_segments as seg1, path_segments as seg2,
   clocks as clk1, clocks as clk2, clocks as clk3,
   cell_templates as t1, cell_templates as t2 
WHERE
    paths.last_clocked_pin_idx=p1.pin_idx 
AND paths.end_pin_idx=p2.pin_idx 
AND paths.last_clocked_pin_net_idx=n1.net_idx 
AND paths.end_pin_net_idx=n2.net_idx 
AND paths.last_clocked_seg_idx=seg1.seg_idx 
AND paths.end_seg_idx=seg2.seg_idx 
AND paths.check_idx=checks.check_idx 
AND seg1.event_reference_clock_idx=clk1.clock_idx 
AND seg2.event_reference_clock_idx=clk2.clock_idx 
AND checks.clk_idx=clk3.clock_idx 
AND checks.ref_node_idx=timing_nodes.node_idx 
AND timing_nodes.net_idx=n3.net_idx 
AND p1.owner_cell_idx=c1.cell_idx 
AND c1.owner_lib_temp_idx=t1.template_idx 
AND p2.owner_cell_idx=c2.cell_idx 
AND c2.owner_lib_temp_idx=t2.template_idx 
ORDER BY (path_idx);

SHOW STATUS LIKE 'last_query_cost';

DROP TABLE cell_templates, cells, checks, clocks,
  nets, path_segments, paths, pins, timing_nodes;