--
-- Table Creation SQL:
--

create table hosts
(
  host_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  hostname CHAR(32),
  PRIMARY KEY (host_id)
);

create table diskdevs
(
  dev_id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  devname  VARCHAR(100),
  host_id  INT UNSIGNED NOT NULL REFERENCES hosts(host_id),
  PRIMARY KEY (dev_id),
  INDEX    devname_idx (devname)
);

create table iostat
(
  iostat_id INT UNSIGNED NOT NULL AUTO_INCREMENT,  /* IOSTAT ID */
  dev_id    INT UNSIGNED NOT NULL REFERENCES diskdevs(dev_id), /* Device id */
  time      TIMESTAMP NOT NULL,     /* Timestamp for data collection */
  rps       DECIMAL(10,1),          /* reads/sec */
  wps       DECIMAL(10,1),          /* writes/sec */
  krps      DECIMAL(10,1),          /* KB read/sec */
  kwps      DECIMAL(10,1),          /* KB written/sec */
  wait      DECIMAL(10,1),          /* Average Queue Length (ms) */
  actv      DECIMAL(10,1),          /* Avg Actively Being Serviced */
  wsvc_t    DECIMAL(10,1),          /* Avg Svc Time in Wait Queue (ms) */
  asvc_t    DECIMAL(10,1),          /* Avg Svc Time for Active Trans (ms) */
  pct_w     TINYINT UNSIGNED,       /* Percent time Trans waiting for Service */
  pct_b     TINYINT UNSIGNED,       /* Percent time Trans in Progress */
  PRIMARY KEY (iostat_id),
  INDEX     iotime_idx (time)
);

--
-- Current creation of temporary table that I'd like to optimize away:
--
CREATE TEMPORARY TABLE devxtimes
SELECT * FROM
  ( SELECT DISTINCT devname, dev_id
    FROM diskdevs
    ORDER BY devname) AS alldevs
  CROSS JOIN
  ( SELECT DISTINCT time
    FROM iostat
    ORDER by time) AS alltimes
ORDER BY time, devname;

--
-- OUTER JOIN that I'd like to simplify/optimize
--
SELECT dt.devname, i.iostat_id, dt.time, i.rps
FROM devxtimes as dt LEFT OUTER JOIN iostat as i
  ON dt.time = i.time AND
     dt.dev_id = i.dev_id
ORDER by dt.time, dt.devname;

--
-- The goal here is to get the following correlated rows from diskdevs and iostat
-- for any arbitrary timestamp range for a particular entry in the hosts table:
--
-- iostat.TIME_1  |  diskdevs.devname1 | iostat.rps | other iostat columns...
--         ...
-- iostat.TIME_1  |  diskdevs.devnameN | iostat.rps | other iostat columns...
--    
--  Rows for TIME_2 through TIME_N-1
--
-- iostat.TIME_N  |  diskdevs.devname1 | iostat.rps | .other iostat columns..
--         ...
-- iostat.TIME_N  |  diskdevs.devnameN | iostat.rps | other iostat columns...
--
-- This is only difficult because not every device in diskdevs has information in the
-- iostat table for every single timestamp, but I need the output to look like it does
-- to make it easier to plot/chart the data that is returned
--
-- At first, I thought this was a simple OUTER JOIN, but it appears to be more difficult
-- and I'd like to get rid of the temporary table too
--