2

I have a table (cumulative_energy_consumptions) defined as:

id int(11) pk
sensor_id int(11) fk
value decimal
recorded_at datetime

with two indexes, one on sensor_id, the other on (sensor_id, recorded_at) and it uses InnoDB engine.

Given a sensor and a datetime, I have to find the row with the maximum 'recorded_at' before that datetime. It can be solved with the following query:

    SELECT MAX(recorded_at)
    FROM cumulative_energy_consumptions
    WHERE sensor_id = 88 AND recorded_at <= '2016-06-29 00:00:00' 

and it is incredibly fast (0.00030 s on my machine), even with a very chatty sensor (id 88) that has more than 300k records.

Instead of a single sensor, I wrote the following query to get the result for many sensors at once:

    SELECT sensor_id, MAX(recorded_at) AS first_before
            FROM cumulative_energy_consumptions
            WHERE 
                sensor_id IN (80, 85, 88, 89) 
                AND 
                recorded_at <= '2016-07-10 00:00:00' 
            GROUP BY sensor_id

and it is also very very fast (0.00055 s)

Now I was trying to write the query for one sensor and many datetimes, and I tried that:

SELECT input_times.*, (
        SELECT MAX(recorded_at)
        FROM cumulative_energy_consumptions
        WHERE sensor_id = 88 AND recorded_at <= input_times.instant
    ) as res
FROM
    (
        SELECT '2016-06-29 00:00:00' as instant
--      UNION SELECT '2016-06-30 00:00:00' as instant
--    UNION SELECT '2016-07-01 00:00:00' as instant
--    UNION SELECT '2016-07-02 00:00:00' as instant
   ) as input_times

when the table created using unions has only one row, it takes 0.0004s and I was expecting that with two rows it would have taken roughly the double, but it completes in 0.2 s; with 4 rows it takes 0.4s and so on.

Why does MySql uses a different strategy with multiple rows instead of applying the strategy for one row many times? Is there a way to rewrite this query to make it as fast as expected?

Thank you

5
  • 1
    I'd try a join first. Something like: SELECT input_times.instant, sensor_id, MAX(recorded_at) AS first_before FROM (SELECT '2016-06-29 00:00:00' as instant UNION ALL ...) AS input_times CROSS JOIN cumulative_energy_consumptions WHERE sensor_id IN (80, 85, 88, 89) AND recorded_at <= input_times.instant GROUP BY input_times.instant, sensor_id; Sep 2, 2016 at 11:51
  • hank you a lot, but the query is still slow as before. It does a range index scan. I also tried to change the index to unique without any result. Should I try to change my dbms? Sep 2, 2016 at 12:59
  • Can you please include the explain output of the queries? What version of mysql are you using? What is the value of optimizer_switch? By the way sensor_id index is superfluous because you already have it in (sensor_id, recorded_at) index. Sep 2, 2016 at 13:15
  • mysql version: 5.7.11 index_merge=on, index_merge_union=on, index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on Sep 2, 2016 at 13:30
  • pastebin.com/raw/iBdJy1mz here you can find the explain for the @ypercubeᵀᴹ comment Sep 2, 2016 at 13:41

2 Answers 2

1

Try this rewriting. It avoids the GROUP BY and should be using the index on (sensor_id, recorded_at) for both the subquery and the join:

SELECT 
    input_times.instant, 
    c.sensor_id, 
    c.recorded_at AS first_before,
    c.value 
FROM 
    ( SELECT 80 AS sensor_id UNION ALL
      SELECT 85  UNION ALL
      SELECT 88  UNION ALL
      SELECT 89
    ) AS s
  CROSS JOIN
    ( SELECT '2016-06-29 00:00:00' AS instant 
      UNION ALL 
      ...
    ) AS input_times
  JOIN  
    cumulative_energy_consumptions AS c
  ON  c.sensor_id = s.sensor_id
  AND c.recorded_at = ( SELECT ci.recorded_at 
                        FROM cumulative_energy_consumptions AS ci
                        WHERE ci.sensor_id = s.sensor_id
                          AND ci.recorded_at <= input_times.instant 
                        ORDER BY ci.recorded_at DESC
                        LIMIT 1
                      ) ;
4
  • Thank you a lot again! This time the query is even slower than before (1.11s vs 0.45s) pastebin.com/raw/5m9kHAVM here you can find the explain output Sep 2, 2016 at 13:47
  • 1
    Is it slow, too, if you remove the c.value from the select list? And did you try the various queries with the change suggested by Rick? Sep 3, 2016 at 11:47
  • At the moment I'm working with a simplified table (see my comment to Rick's answer) and the query performances are the same. Sep 5, 2016 at 5:54
  • I also tried to created another index on (recorded_at, sensor_id) and forcing the query to use it, without any result. Things are getting worse if I use as input a date in august (instead of july): I think this means that under the hood MySQL performs a full scan up to the input date. I'm trying to understand if here I can find the solution Sep 5, 2016 at 6:02
1

This may help the other suggestions, so do it regardless.

Get rid if id:

CREATE TABLE cumulative_energy_consumptions (
    sensor_id SMALLINT UNSIGNED NOT NULL,  -- note: smaller
    value ... NOT NULL,
    recorded_at DATETIME NOT NULL,
    PRIMARY KEY(sensor_id, recorded_at)  -- changed
) ENGINE=InnoDB;

You did mention the details of the datatype for value. Don't get excessive on the number of digits if you use DECIMAL. Also, consider FLOAT as an alternative. (Do not use FLOAT(m,n).)

The UNION of a single column is a terrible idea. WHERE recorded_at IN ('...', '...', ...) much better. But if recorded_at <= '2016-07-10 00:00:00' is "correct", then use it.

Beware of any time less than 1ms. It could be going through the Query cache and not actually running the query. Use SELECT SQL_NO_CACHE ... to get 'fair' timings.

3
  • I created another table called 'cumulative_dup' following all your guidelines, but nothing really changed. I also dropped "value" column to be sure that its does not create any issue Sep 5, 2016 at 5:46
  • SQL_NO_CACHE does not affect my timings: every sub-millisecond query is still as fast as before. In those cases MySQL does a very great job with indexes, but it seems to ignore them as we add more datetime values as input Sep 5, 2016 at 5:49
  • When asking for more than about 20% of a table, the index is ignored since it is likely to be faster to do a table scan. (The 20% is not a fixed number.)
    – Rick James
    Sep 5, 2016 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.