Join three MySQL queries to one to get it faster

Question

I have three MySQL queries, how to join it to one to get result faster? Is it possible to do it with single query and if it then if it was faster than separate three queries? (I can have up to 1.5M records in table - measurements from 3 month period)

• id - auto increment, int
• phase - tinyint, could be only 1,2 or 3
• time - datetime, time is same for each phase group 1,2,3
• voltage - float

Sample data:

+----+---------------------+-------+---------+
| id | time                | phase | voltage |
+----+---------------------+-------+---------+
|  1 | 2021-01-31 19:55:01 |     1 |  235.77 |
|  2 | 2021-01-31 19:55:01 |     2 |  237.94 |
|  3 | 2021-01-31 19:55:01 |     3 |  224.63 |
|  4 | 2021-01-31 19:55:06 |     1 |  236.41 |
|  5 | 2021-01-31 19:55:06 |     2 |  238.17 |
|  6 | 2021-01-31 19:55:06 |     3 |  224.61 |
|  7 | 2021-01-31 19:55:11 |     1 |  236.45 |
|  8 | 2021-01-31 19:55:11 |     2 |  237.87 |
|  9 | 2021-01-31 19:55:11 |     3 |   223.4 |
+----+---------------------+-------+---------+

Query #1:

SELECT time, voltage AS L1
FROM measurements
WHERE phase = 1
ORDER BY id 

Query #2:

SELECT time, voltage AS L2
FROM measurements
WHERE phase = 2
ORDER BY id

Query #3:

SELECT time, voltage AS L3
FROM measurements
WHERE phase = 3
ORDER BY id  

Result after should be like that:

+----+---------------------+--------+--------+--------+
| id | time                |   L1   |   L2   |   L3   |
+----+---------------------+--------+--------+--------+
|  1 | 2021-01-31 19:55:01 | 235.77 | 237.94 | 224.63 |
|  2 | 2021-01-31 19:55:06 | 236.41 | 238.17 | 224.61 |
|  3 | 2021-01-31 19:55:11 | 236.45 | 237.87 | 223.4  |
+----+---------------------+--------+--------+--------+

EDIT: after all, I got a query:

SELECT 
    time,
    GROUP_CONCAT(IF(phase = 1, voltage, NULL)) AS 'L1',
    GROUP_CONCAT(IF(phase = 2, voltage, NULL)) AS 'L2',
    GROUP_CONCAT(IF(phase = 3, voltage, NULL)) AS 'L3'
FROM 
    `measurements`
GROUP BY 
    time

and now results looks like:

+---------------------+--------------------+--------------------+--------------------+
| time                | L1                 | L2                 | L3                 |
+---------------------+--------------------+--------------------+--------------------+  

| 2021-01-31 19:55:01 | 235.77000427246094 | 237.94000244140625 | 224.6300048828125  |
| 2021-01-31 19:55:06 | 236.41000366210938 | 238.1699981689453  | 224.61000061035156 |
| 2021-01-31 19:55:11 | 236.4499969482422  | 237.8699951171875  | 223.39999389648438 |
+---------------------+--------------------+--------------------+--------------------+

Why now voltage values are so inaccurate, all voltage stored values are with max 2 number after decimal point I don't do any math in query... then why got this strange values?

Answer 1

I would highly recommend to change the structure a bit. What would you say for:

• table measurements with columns:

  • id
  • time

• table measurements_results with columns:

  • id
  • measurement_id
  • phase
  • voltage

Table measurements_results must have foreign key on measurement_id. On phase column you could put regular index.

Than the query will look like this:

select m.id measurement_id, m.time, mr_l1.voltage L1, mr_l2.voltage L2, mr_l3.voltage L3
from measurements m
left join measurements_results mr_l1
    on mr_l1.measurement_id = m.id
    and mr_l1.phase = 1
left join measurements_results mr_l2
    on mr_l2.measurement_id = m.id
    and mr_l2.phase = 2
left join measurements_results mr_l3
    on mr_l3.measurement_id = m.id
    and mr_l3.phase = 3

[Edit] Left joins are for safety because this db structure does not ensure you will always have voltage for all 3 phases. So you could add coalesce(voltage, 0) to not get null in result.

Answer 2

You can do it in a simple way with group by and using CASE statements:

SELECT time, max(case phase
                    when 1 then voltage
                    else 0
                  end) as L1, 
              max(case phase
                    when 2 then voltage
                    else 0
                  end) as L2, 
              max(case phase
                    when 3 then voltage
                    else 0
                  end) as L3
FROM measurements
group by time
ORDER BY time

Answer 3

The issue is that GROUP_CONCAT() has to convert the values to strings. It looks like they are floating point values -- so the conversion is rarely accurate.

If you really wanted to convert to a string, then use FORMAT() to get the format you want. However, I would preserve the original type using:

SELECT time,
       MAX(CASE WHEN phase = 1 THEN voltage END) AS L1,
       MAX(CASE WHEN phase = 2 THEN voltage END) AS L2,
       MAX(CASE WHEN phase = 3 THEN voltage END) AS L3
FROM measurements
GROUP BY time;

Notes:

• You don't generally need backticks on identifiers, such as measurements.
• I much, much prefer CASE because it is standard SQL, so I replaced that.
• Do not use single quotes for column aliases. It is too easy to confuse them with strings. And, they are not needed either.

Answer 4

I'll argue for fixing it on input; this will probably be better in the long run. But... What other SELECTs do you need to do against the data?

CREATE TABLE measurements (
    dt DATETIME NOT NULL,
    v1 DECIMAL(5,2) NOT NULL,     -- or whatever is appropriate; FLOAT is a reasonable option
    v2 DECIMAL(5,2) NOT NULL,
    v3 DECIMAL(5,2) NOT NULL,
    PRIMARY KEY (dt)
) ENGINE=InnoDB

Notes:

• If there are other columns, we need to know about them
• If the data comes in as 3 records, then either collect them and do a single INSERT or do 1 INSERT and 2 UPDATEs. For this latter case, consider NULL.
• "235.77000427246094" has to do with when MySQL decides to perform computations in DOUBLE. Doing pivoting a different way avoids such.