MySQL - How to fetch MIN / MAX Date from MIN / MAX Value

Question

I have a question about how I can get all the dates from all the MIN/MAX values from multiple columns and how to optimize it. I've tried to get it to run reasonably for a long time now, and I have almost given up :-(

The Database stores a lot of temperatures from various devices. and is structured in this way.

ID, User, DateTime, Temp1, Temp2, Temp3 and so on.

And now I want to extract all the dates and values for the MIN and MAX for each of the measurements (Date for MIN Temp1, Date for MAX Temp1, ....)

To get the MIN/MAX values is very easy and fast.

SELECT
MIN(Modul_FremL) as Min_Temp1,
MAX(Modul_FremL) as Max_Temp1,
MIN(Modul_ReturL) as Min_Temp2,
MAX(Modul_ReturL) as Max_Temp2,
MIN(Modul_Gas) as Min_Temp3,
MAX(Modul_Gas) as Max_Temp3,
MIN(Modul_FB) as Min_Temp4,
MAX(Modul_FB) as Max_Temp4,
MIN(Modul_SB) as Min_Temp5,
MAX(Modul_SB) as Max_Temp5,
MIN(Temp_Kedel) as Min_Temp6,
MAX(Temp_Kedel) as Max_Temp6,
MIN(Temp_Central) as Min_Temp7,
MAX(Temp_Central) as Max_Temp7,
MIN(Temp_VVB) as Min_Temp8,
MAX(Temp_VVB) as Max_Temp8,
MIN(Temp_Ude) as Min_Temp9,
MAX(Temp_Ude) as Max_Temp9
FROM pillestat_data_1
LIMIT 1

To get this data it only to it takes only 0.2004 seconds to obtain all the data from a database with over 300,000 rows.

But as I also need the datetime for the MIN/MAX have I tried with this.

SELECT DatoTid, MIN(Modul_FremL) as Temperatur FROM pillestat_data_1 WHERE Modul_FremL = (SELECT MIN(Modul_FremL) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Modul_FremL) as Temperatur FROM pillestat_data_1 WHERE Modul_FremL = (SELECT MAX(Modul_FremL) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MIN(Modul_ReturL) as Temperatur FROM pillestat_data_1 WHERE Modul_ReturL = (SELECT MIN(Modul_ReturL) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Modul_ReturL) as Temperatur FROM pillestat_data_1 WHERE Modul_ReturL = (SELECT MAX(Modul_ReturL) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MIN(Modul_Gas) as Temperatur FROM pillestat_data_1 WHERE Modul_Gas = (SELECT MIN(Modul_Gas) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Modul_Gas) as Temperatur FROM pillestat_data_1 WHERE Modul_Gas = (SELECT MAX(Modul_Gas) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MIN(Modul_FB) as Temperatur FROM pillestat_data_1 WHERE Modul_FB = (SELECT MIN(Modul_FB) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Modul_FB) as Temperatur FROM pillestat_data_1 WHERE Modul_FB = (SELECT MAX(Modul_FB) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MIN(Modul_SB) as Temperatur FROM pillestat_data_1 WHERE Modul_SB = (SELECT MIN(Modul_SB) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Modul_SB) as Temperatur FROM pillestat_data_1 WHERE Modul_SB = (SELECT MAX(Modul_SB) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MIN(Temp_Kedel) as Temperatur FROM pillestat_data_1 WHERE Temp_Kedel = (SELECT MIN(Temp_Kedel) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Temp_Kedel) as Temperatur FROM pillestat_data_1 WHERE Temp_Kedel = (SELECT MAX(Temp_Kedel) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MIN(Temp_Central) as Temperatur FROM pillestat_data_1 WHERE Temp_Central = (SELECT MIN(Temp_Central) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Temp_Central) as Temperatur FROM pillestat_data_1 WHERE Temp_Central = (SELECT MAX(Temp_Central) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MIN(Temp_VVB) as Temperatur FROM pillestat_data_1 WHERE Temp_VVB = (SELECT MIN(Temp_VVB) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Temp_VVB) as Temperatur FROM pillestat_data_1 WHERE Temp_VVB = (SELECT MAX(Temp_VVB) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MIN(Temp_Ude) as Temperatur FROM pillestat_data_1 WHERE Temp_Ude = (SELECT MIN(Temp_Ude) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Temp_Ude) as Temperatur FROM pillestat_data_1 WHERE Temp_Ude = (SELECT MAX(Temp_Ude) FROM pillestat_data_1 LIMIT 1)

It's really just 18 queries, but it takes alot of time. 1.9866 seconds for the 300.000 rows.

These 300,000 rows are only a small database, expects to get up on about 18 milion rows.

I've also tried with this one

SELECT
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_FremL = (SELECT MIN(Modul_FremL) FROM pillestat_data_1 )) AS Min_DT_Temp1, MIN(Modul_FremL) AS Temp_Min_Temp1,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_FremL = (SELECT MAX(Modul_FremL) FROM pillestat_data_1 )) AS Max_DT_Temp1, MAX(Modul_FremL) AS Temp_Max_Temp1,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_ReturL = (SELECT MIN(Modul_ReturL) FROM pillestat_data_1 )) AS Min_DT_Temp2, MIN(Modul_ReturL) AS Temp_Min_Temp2,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_ReturL = (SELECT MAX(Modul_ReturL) FROM pillestat_data_1 )) AS Max_DT_Temp2, MAX(Modul_ReturL) AS Temp_Max_Temp2,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_Gas = (SELECT MIN(Modul_Gas) FROM pillestat_data_1 )) AS Min_DT_Temp3, MIN(Modul_Gas) AS Temp_Min_Temp3,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_Gas = (SELECT MAX(Modul_Gas) FROM pillestat_data_1 )) AS Max_DT_Temp3, MAX(Modul_Gas) AS Temp_Max_Temp3,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_FB = (SELECT MIN(Modul_FB) FROM pillestat_data_1 )) AS Min_DT_Temp4, MIN(Modul_FB) AS Temp_Min_Temp4,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_FB = (SELECT MAX(Modul_FB) FROM pillestat_data_1 )) AS Max_DT_Temp4, MAX(Modul_FB) AS Temp_Max_Temp4,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_SB = (SELECT MIN(Modul_SB) FROM pillestat_data_1 )) AS Min_DT_Temp5, MIN(Modul_SB) AS Temp_Min_Temp5,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_SB = (SELECT MAX(Modul_SB) FROM pillestat_data_1 )) AS Max_DT_Temp5, MAX(Modul_SB) AS Temp_Max_Temp5,
(SELECT DatoTid FROM pillestat_data_1 WHERE Temp_Kedel = (SELECT MIN(Temp_Kedel) FROM pillestat_data_1 )) AS Min_DT_Temp6, MIN(Temp_Kedel) AS Temp_Min_Temp6,
(SELECT DatoTid FROM pillestat_data_1 WHERE Temp_Kedel = (SELECT MAX(Temp_Kedel) FROM pillestat_data_1 )) AS Max_DT_Temp6, MAX(Temp_Kedel) AS Temp_Max_Temp6,
(SELECT DatoTid FROM pillestat_data_1 WHERE Temp_Central = (SELECT MIN(Temp_Central) FROM pillestat_data_1 )) AS Min_DT_Temp7, MIN(Temp_Central) AS Temp_Min_Temp7,
(SELECT DatoTid FROM pillestat_data_1 WHERE Temp_Central = (SELECT MAX(Temp_Central) FROM pillestat_data_1 )) AS Max_DT_Temp7, MAX(Temp_Central) AS Temp_Max_Temp7,
(SELECT DatoTid FROM pillestat_data_1 WHERE Temp_VVB = (SELECT MIN(Temp_VVB) FROM pillestat_data_1 )) AS Min_DT_Temp8, MIN(Temp_VVB) AS Temp_Min_Temp8,
(SELECT DatoTid FROM pillestat_data_1 WHERE Temp_VVB = (SELECT MAX(Temp_VVB) FROM pillestat_data_1 )) AS Max_DT_Temp8, MAX(Temp_VVB) AS Temp_Max_Temp8,
(SELECT DatoTid FROM pillestat_data_1 WHERE Temp_Ude = (SELECT MIN(Temp_Ude) FROM pillestat_data_1 )) AS Min_DT_Temp9, MIN(Temp_Ude) AS Temp_Min_Temp9,
(SELECT DatoTid FROM pillestat_data_1 WHERE Temp_Ude = (SELECT MAX(Temp_Ude) FROM pillestat_data_1 )) AS Max_DT_Temp9, MAX(Temp_Ude) AS Temp_Max_Temp9
FROM pillestat_data_1
LIMIT 1

But for some reason it will not work on all of the columns. It works on approximately half of the columns.

So I hope that there is someone in here who can help me optimize my code.

// KIM

Have made a SQLFIDDLE if you want to test.

http://sqlfiddle.com/#!9/2b1c8f/16

Answer 1

A normalized table design would be

devices table
-------------
id
name
...


temperatures table
------------------
id
user_id
datetime
device_id
temp

Foreign keys automatically get indexed. Also add an index for the datetime column if you do date dependent selects.

Then to get the min and max tmp of all devices do

select d.name, min(t.temp) as minTemp, max(t.temp) as maxTemp
from temperratures t
join devices d on d.id = t.device_id
group by d.name

That should return results in a few milliseconds even if you have millions of records.