Query with long IN(...) list in WHERE about 40 times slower

# Time: 181122  9:56:07

# User@Host: 

# Thread_id: 6298341  Schema: leitsystem  QC_hit: No

# Query_time: 6.211773  Lock_time: 0.000545  Rows_sent: 25  Rows_examined: 825043

# Rows_affected: 0

# Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No

# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes

#

# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra

# explain: 1    SIMPLE  zr_te_value     range   PRIMARY,ts      PRIMARY 9       NULL    420882  412509.00       100.00  100.00  Using where; Using temporary; Using filesort

#

SET timestamp=1542876967;

SELECT CONCAT("9906643000004NEXT", LPAD(next_id_int, 16, "0")) AS next_id, ts - MOD(ts, 300), ROUND(AVG(value), 3), AVG(quality) FROM leitsystem.zr_te_value WHERE next_id_in

t IN (6447,5272,...,3104) 

AND ts >= 1542668700 AND ts < 1542755400 AND zr_art = 12 GROUP BY next_id, ts DIV 300 ORDER BY ts ASC LIMIT 0, 25;;

# Time: 181122  9:20:42

# User@Host: 

# Thread_id: 82  Schema: leitsystem  QC_hit: No

# Query_time: 214.707173  Lock_time: 0.000781  Rows_sent: 25  Rows_examined: 211389323

# Rows_affected: 0  Bytes_sent: 1773

# Tmp_tables: 3  Tmp_disk_tables: 0  Tmp_table_sizes: 92843912

# Full_scan: Yes  Full_join: Yes  Tmp_table: Yes  Tmp_table_on_disk: No

# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes

#

# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra

# explain: 1    PRIMARY <subquery2>     ALL     distinct_key    NULL    NULL    NULL    1548    1548.00 100.00  100.00  Using temporary; Using filesort

# explain: 1    PRIMARY zr_te_value     ref     PRIMARY,ts      PRIMARY 5       const,tvc_0._col_1      218     136287.92       100.00  0.20    Using where

# explain: 2    MATERIALIZED    <derived3>      ALL     NULL    NULL    NULL    NULL    1548    1548.00 100.00  100.00

# explain: 3    DERIVED NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used

#

use leitsystem;

SET timestamp=1542874842;

SELECT CONCAT("9906643000004NEXT", LPAD(next_id_int, 16, "0")) AS next_id, ts - MOD(ts, 300), ROUND(AVG(value), 3), AVG(quality) FROM leitsystem.zr_te_value WHERE next_id_in

t IN (6447,5272,...,3104) 

AND ts >= 1542668700 AND ts < 1542755400 AND zr_art = 12 GROUP BY next_id, ts DIV 300 ORDER BY ts ASC LIMIT 0, 25;;