Re: Point-In-Time Architecture performance

[Meir Guttman]

Hi Peter!

Well, here are the CREATE TABLE queries:

CREATE TABLE `list_changes` (
  `SecurityID` char(8) NOT NULL,
  `TradingDay` date NOT NULL,
  `To_list` enum('Illiquid','Maintenance','Suspended','Primary','Delisted') NOT NULL,
  PRIMARY KEY (`SecurityID`,`TradingDay`),
  KEY `List_date` (`TradingDay`),
  KEY `List_SecID` (`SecurityID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `paidupcapital_2` (
  `SecurityID` char(8) NOT NULL,
  `TradingDay` date NOT NULL,
  `Units` bigint(20) NOT NULL,
  PRIMARY KEY (`SecurityID`,`TradingDay`),
  KEY `KY_PUC_SecID` (`SecurityID`),
  KEY `KY_PUC_Date` (`TradingDay`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

EXTENDED EXPLAIN for `list_changes`:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: l
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5994
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: l2
         type: ref
possible_keys: PRIMARY,List_date,List_SecID
          key: PRIMARY
      key_len: 24
          ref: investments.l.SecurityID
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index

Following the above with "SHOW WARNINGS" returned (edited with CR):

*************************** 1. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 'investments.l.SecurityID' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: select `investments`.`l`.`SecurityID` AS `SecurityID`,`investments`.`l`.`To_list` AS `to_list`
from `investments`.`list_changes` `l`
where ((`investments`.`l`.`To_list` <> 'Delisted')
   and (`investments`.`l`.`TradingDay` =
       (select max(`investments`.`l2`.`TradingDay`) from `investments`.`list_changes` `l2`
        where ((`investments`.`l`.`SecurityID` = `investments`.`l2`.`SecurityID`)
        and (`investments`.`l2`.`TradingDay` < '2014-6-1')))))
2 rows in set (0.00 sec)

EXTENDED EXPLAIN for `paidupcapital_2`:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: p
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 41485
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: p2
         type: ref
possible_keys: PRIMARY,KY_PUC_SecID,KY_PUC_Date
          key: PRIMARY
      key_len: 24
          ref: investments.p.SecurityID
         rows: 23
     filtered: 100.00
        Extra: Using where; Using index
2 rows in set, 2 warnings (0.00 sec)

And again, following the above with "SHOW WARNINGS" yields:

*************************** 1. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 'investments.p.SecurityID' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: select `investments`.`p`.`SecurityID` AS `SecurityID`,`investments`.`p`.`Units` AS `units`
from `investments`.`paidupcapital_2` `p`
where (`investments`.`p`.`TradingDay` =
      (select max(`investments`.`p2`.`TradingDay`) from `investments`.`paidupcapital_2` `p2`
       where ((`investments`.`p`.`SecurityID` = `investments`.`p2`.`SecurityID`)
         and (`investments`.`p2`.`TradingDay` < '2014-6-1'))))
2 rows in set (0.00 sec)

Regards,
Meir