---- 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 devxtimesSELECT * FROM ( SELECT DISTINCT devname, dev_id FROM diskdevs ORDER BY devname) AS alldevs CROSS JOIN ( SELECT DISTINCT time FROM iostat ORDER by time) AS alltimesORDER BY time, devname;---- OUTER JOIN that I'd like to simplify/optimize--SELECT dt.devname, i.iostat_id, dt.time, i.rpsFROM devxtimes as dt LEFT OUTER JOIN iostat as i ON dt.time = i.time AND dt.dev_id = i.dev_idORDER 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--