Query runs too slow and even it stops because exceded of time with 17000 rows-CodePudding

I have table 1:

historial_id	timestamp	address	value	insertion_time
1	2022-01-29	1	84	2022-01-31
2	2022-01-29	2	40	2022-01-31
3	2022-01-30	1	84	2022-01-31
4	2022-01-30	2	41	2022-01-31
5	2022-01-30	2	41	2022-01-31

(sometimes it has repeated rows)

...

I need a Query to get:

timestamp	value(address 1)	value(address 2)
2022-01-29	84	40
2022-01-30	84	41

......

I tried with:

SELECT timestamp, ( SELECT value 
                   FROM historical 
                   WHERE register_type=11 
                   AND address=2
                   AND timestamp=t1.timestamp
                   GROUP BY value 
                   ) AS CORRIENTE_mA,
                ( SELECT value 
                  FROM historical 
                  WHERE register_type=11 
                  AND address=1 
                  AND timestamp=t1.timestamp 
                  GROUP BY value ) AS Q_M3pH
FROM historical AS t1 
GROUP BY timestamp;

But it's too slow, it even stops because of exceeded time.

I tried with distinct too instead of group by

CodePudding user response：

I think you need dynamic pivot. Please try and avoid MySQL reserved words like timestamp.

Below query return only the max value for address 1 and 2 grouping by timestamp.

This is a simplified version of your query :

select
     `timestamp`
   , max(case when address=1 then value  end) as value_address1
   , max(case when address=2 then value  end) as value_address2

from historical
group by `timestamp`;

Result:

  timestamp   value_address1  value_address2
  2022-01-29       84            40
  2022-01-30       84            41

Demo