67734

What I can use subquery instead for search MAX()?

Question:

I have table, 'master_log', where I store all data from all action. Each records is connected together by 'm_id':

m_id | value_type_id | value ------------------------------------------ 1 | room | ONE 1 | temperature | 23 1 | humidity | 55 1 | timestamp | 2016-11-01 00:00:01 2 | room | TWO 2 | timestamp | 2016-11-01 00:10:51 2 | temperature | 24 3 | room | ONE 3 | temperature | 24 3 | timestamp | 2016-11-01 00:18:24 ------------------------------------------

I need the maximum temperature from room ONE. The query that I use now is here:

SELECT MAX(value) FROM master_log WHERE value_type_id = 'temperature' AND m_id IN (SELECT m_id FROM master_log WHERE value = 'ONE')

When I start measuring, the query is quick, but now with millions of records it is not too quick, and I looking for the quickest method. Any idea please?

Answer1:

E.g.:

SELECT m_id , MAX(CASE WHEN value_type_id = 'room' THEN value END) room , MAX(CASE WHEN value_type_id = 'temperature' THEN value END) temperature , MAX(CASE WHEN value_type_id = 'humidity' THEN value END) humidity , MAX(CASE WHEN value_type_id = 'timestamp' THEN value END) timestamp FROM master_log GROUP BY m_id HAVING room = 'ONE' ORDER BY temperature DESC LIMIT 1;

... but, appropriately indexed, Martin's solution will be faster

Recommend