[#MDEV-17795] Query with long IN(...) list in WHERE about 40 times slower

[MDEV-17795] Query with long IN(...) list in WHERE about 40 times slower Created: 2018-11-22 Updated: 2023-12-07
Status:	Stalled
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	10.3.11
Fix Version/s:	10.4

Type:

Bug

Priority:

Major

Reporter:

Frank Sagurna

Assignee:

Igor Babaev

Resolution:

Unresolved

Votes:

Labels:

regression

Environment:

Ubuntu 18.04
MariaDB Repository
RAM 32GB
8 CPU cores

Issue Links:

Problem/Incident
is caused by	~~MDEV-12176~~	Transform [NOT] IN predicate with lon...	Closed
is caused by	~~MDEV-20105~~	Case for bringing in_subquery_convers...	Closed

Description

Query that used to take about 5 seconds in MariaDB 10.2.19 now needs 200 seconds.
List in IN is about 1500 values.

MariaDB 10.2.19:

# 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;;

MariaDB 10.3.11:

# 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;;

Already discussed this in ~~MDEV-12176~~

Comments

Comment by Frank Sagurna [ 2018-11-22 ]

Added the "LIMIT 0, 25;" that was added by phpmyadmin, that i did not copy from the original query to make the log output consistent with "Rows_sent:". Original query will not have the LIMIT.

Comment by Igor Babaev [ 2018-11-25 ]

Frank,
Thank you for the report, but how am I supposed to reproduce the problem?
Why did you change the query in the report?
Originally you had in your query the predicate
next_id_int IN ('0000000000006447', '0000000000005272', ... )
Now you have
next_id_int IN (6447,5272,...,3104)
Are the constants in the list are all integers? (nothing like '00000005071' in the list)?
Do you really have the problem with the predicate when each constant in the list is an integer literal?
Anyway as you do not provide any test case I have to re-assign the bug to one of our engineer who probably will try to build such a test case by pulling out more info from you.

Comment by VAROQUI Stephane [ 2018-11-25 ]

Igor,

Frank is trying to demonstrate that the issue is not based on datatype conversion but on lack of hash index on the temporary table ! It was not change to add confusion ! but more to demonstrate that the optimisation is not good compare to a plab where the not in predicate is covered by ICP on a covering index.

Comment by Igor Babaev [ 2018-11-25 ]

Stephane,
I really don't follow you:
I definitely see the range access in 10.2.
In 10.3 after the transformation into IN subquery there should be the ref access from the subquery to the table leitsystem.zr_te_value iff the types of all the constants are integers and the type of next_id_in is integer.

Comment by Frank Sagurna [ 2018-11-26 ]

As Stephane said, i wanted to show that the bug is not based on datatype conversion.

Comment by Alice Sherepa [ 2018-11-26 ]

gunni Could you please add the output of

SHOW CREATE TABLE leitsystem.zr_te_value;

and your .cnf files

Comment by Frank Sagurna [ 2018-11-26 ]

MariaDB [leitsystem]> SHOW CREATE TABLE leitsystem.zr_te_value;

+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table       | Create Table                                                                                                                                                                                                                                                                                                                             |

+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| zr_te_value | CREATE TABLE `zr_te_value` (

  `zr_art` tinyint(11) NOT NULL,

  `next_id_int` int(11) NOT NULL,

  `ts` int(11) NOT NULL,

  `value` double NOT NULL,

  `quality` tinyint(4) NOT NULL,

  PRIMARY KEY (`zr_art`,`next_id_int`,`ts`),

  KEY `ts` (`ts`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

 PARTITION BY HASH (`next_id_int`)

PARTITIONS 8 |

+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.000 sec)

[mysqld]

# * Basic Settings

user            = mysql

pid-file        = /var/run/mysqld/mysqld.pid

socket          = /var/run/mysqld/mysqld.sock

port            = 3306

basedir         = /usr

datadir         = /var/lib/mysql

tmpdir          = /tmp

lc-messages-dir = /usr/share/mysql

skip-external-locking

bind-address            = 0.0.0.0

# * Fine Tuning

connect_timeout         = 5

wait_timeout            = 7200

key_buffer_size         = 128M

max_allowed_packet      = 16M

thread_stack            = 192K

tmp_table_size          = 384M

max_heap_table_size     = 384M

sort_buffer_size        = 8M

table_open_cache        = 24k

open_files_limit        = 16k

myisam-recover-options = BACKUP

max_connections        = 200

# * Query Cache Configuration

query_cache_limit       = 0

query_cache_size        = 0

query_cache_type = 0

# * Logging and Replication

server-id               = 106

report-host             = NX-MARIA2

log_bin                 = /var/log/mysql/mysql-bin.log

relay-log               = mysql-relay-bin

expire_logs_days        = 10

max_binlog_size         = 100M

binlog_format           = mixed

binlog_cache_size       = 128k

log-slave-updates

character-set-server  = utf8

collation-server      = utf8_general_ci

character_set_server   = utf8

collation_server       = utf8_general_ci

plugin-load-add         = auth_socket.so

# ssl

ssl=1

ssl-ca=/etc/mysql/ssl/ca-cert.pem

ssl-cert=/etc/mysql/ssl/server-cert.pem

ssl-key=/etc/mysql/ssl/server-key.pem

# innodb

innodb_buffer_pool_size = 22G

innodb_log_file_size   = 128M

innodb_flush_method    = O_DIRECT

innodb_flush_log_at_trx_commit=2

# logging

log_error = /var/log/mysql/error.log

slow_query_log          = 1

slow_query_log_file     = /var/log/mysql/mysql-slow.log

long_query_time = 3

log_slow_verbosity      = query_plan,innodb,explain

[mysqldump]

quick

quote-names

max_allowed_packet      = 16M

[mysqld_safe]

skip_log_error

syslog

Comment by Frank Sagurna [ 2018-11-26 ]

Ah, now that you mention that i see what can be the reason. In my query i always use a fixed zr_art, so the SELECT can use the index. In the case the subquery gets "optimized" the subquery cannot use the index anymore. Can this be?
You decide if it is a bug, or not.

Comment by VAROQUI Stephane [ 2018-11-26 ]

It's for sure a regression

CREATE OR REPLACE TABLE t (c1 int,c2 int, key i(c1,c2));

INSERT INTO t VALUES

(1,1),

(1,2),

(1,3),

(2,1),

(2,3);

set profiling=1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT distinct c1 from t WHERE c2 NOT IN(2,4,6,7,8,9,13,14,15,16,17,18,19,20,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,1015,1016,1017,1018,1019,1020,1021,1022,1023,1024,1025,1026,1027,1028,1029,1030,1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1043,1044,1045,1046,1047,1048,1049,1050,1051,1052,1053,1054,1055,1056,1057,1058,1059,1060,1061,1062,1063,1064,1065,1066,1067,1068,1069,1070,1071,1072,1073,1074,1075,1076,1077,1078,1079,1080,1081,1082,1083,1084,1085,1086,1087,1088,1089,1090,1091,1092,1093,1094,1095,1096,1097,1098,1099,1100,1101,1102,1103,1104,1105,1106,1107,1108,1109,1110,1111,1112,1113,1114,1115,1116,1117,1118,1119,1120,1121,1122,1123,1124,1125,1126,1127,1128,1129,1130,1131,1132,1133,1134,1135,1136,1137,1138,1139,1140,1141,1142,1143,1144,1145,1146,1147,1148,1149,1150,1151,1152,1153,1154,1155,1156,1157,1158,1159,1160,1161,1162,1163,1164,1165,1166,1167,1168,1169,1170,1171,1172,1173,1174,1175,1176,1177,1178,1179,1180,1181,1182,1183,1184,1185,1186,1187,1188,1189,1190,1191,1192,1193,1194,1195,1196,1197,1198,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1210,1211,1212,1213,1214,1215,1216,1217,1218,1219,1220,1221,1222,1223,1224,1225,1226,1227,1228,1229,1230,1231,1232,1233,1234,1235,1236,1237,1238,1239,1240,1241,1242,1243,1244,1245,1246,1247,1248,1249,1250,1251,1252,1253,1254,1255,1256,1257,1258,1259,1260,1261,1262,1263,1264,1265,1266,1267,1268,1269,1270,1271,1272,1273,1274,1275,1276,1277,1278,1279,1280,1281,1282,1283,1284,1285,1286,1287,1288,1289,1290,1291,1292,1293,1294,1295,1296,1297,1298,1299,1300,1301,1302,1303,1304,1305,1306,1307,1308,1309,1310,1311,1312,1313,1314,1315,1316,1317,1318,1319,1320,1321,1322,1323,1324,1325,1326,1327,1328,1329,1330,1331,1332,1333,1334,1335,1336,1337,1338,1339,1340,1341,1342,1343,1344,1345,1346,1347,1348,1349,1350,1351,1352,1353,1354,1355,1356,1357,1358,1359,1360,1361,1362,1363,1364,1365,1366,1367,1368,1369,1370,1371,1372,1373,1374,1375,1376,1377,1378,1379,1380,1381,1382,1383,1384,1385,1386,1387,1388,1389,1390,1391,1392,1393,1394,1395,1396,1397,1398,1399,1400,1401,1402,1403,1404,1405,1406,1407,1408,1409,1410,1411,1412,1413,1414,1415,1416,1417,1418,1419,1420,1421,1422,1423,1424,1425,1426,1427,1428,1429,1430,1431,1432,1433,1434,1435,1436,1437,1438,1439,1440,1441,1442,1443,1444,1445,1446,1447,1448,1449,1450,1451,1452,1453,1454,1455,1456,1457,1458,1459,1460,1461,1462,1463,1464,1465,1466,1467,1468,1469,1470,1471,1472,1473,1474,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1485,1486,1487,1488,1489,1490,1491,1492,1493,1494,1495,1496,1497,1498,1499,1500,1501,1502,1503,1504,1505,1506,1507,1508,1509,1510,1511,1512,1513,1514,1515,1516,1517,1518,1519,1520,1521,1522,1523,1524,1525,1526 ) GROUP BY c1;

+------+

| c1   |

+------+

|    1 |

|    2 |

+------+

2 rows in set (0.00 sec)

MariaDB [test]> show profile for query 1;

+------------------------+----------+

| Status                 | Duration |

+------------------------+----------+

| Starting               | 0.000275 |

| Checking permissions   | 0.000006 |

| Opening tables         | 0.000009 |

| After opening tables   | 0.000005 |

| System lock            | 0.000005 |

| Table lock             | 0.000007 |

| Init                   | 0.000103 |

| Optimizing             | 0.000102 |

| Statistics             | 0.000020 |

| Preparing              | 0.000010 |

| Optimizing             | 0.000005 |

| Statistics             | 0.000039 |

| Preparing              | 0.000016 |

| Sorting result         | 0.000005 |

| Executing              | 0.000004 |

| Sending data           | 0.000026 |

| Executing              | 0.000004 |

| Sending data           | 0.000097 |

| Removing tmp table     | 0.000005 |

| Sending data           | 0.000516 |

| End of update loop     | 0.000006 |

| Query end              | 0.000004 |

| Commit                 | 0.000007 |

| Closing tables         | 0.000003 |

| Removing tmp table     | 0.000004 |

| Closing tables         | 0.000003 |

| Unlocking tables       | 0.000003 |

| Closing tables         | 0.000006 |

| Starting cleanup       | 0.000003 |

| Freeing items          | 0.000007 |

| Removing tmp table     | 0.000004 |

| Freeing items          | 0.000012 |

| Updating status        | 0.000027 |

| Reset for next command | 0.000005 |

+------------------------+----------+

34 rows in set (0.00 sec)

MariaDB [test]> explain SELECT distinct c1 from t WHERE c2 NOT IN(2,4,6,7,8,9,13,14,15,16,17,18,19,20,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,1015,1016,1017,1018,1019,1020,1021,1022,1023,1024,1025,1026,1027,1028,1029,1030,1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1043,1044,1045,1046,1047,1048,1049,1050,1051,1052,1053,1054,1055,1056,1057,1058,1059,1060,1061,1062,1063,1064,1065,1066,1067,1068,1069,1070,1071,1072,1073,1074,1075,1076,1077,1078,1079,1080,1081,1082,1083,1084,1085,1086,1087,1088,1089,1090,1091,1092,1093,1094,1095,1096,1097,1098,1099,1100,1101,1102,1103,1104,1105,1106,1107,1108,1109,1110,1111,1112,1113,1114,1115,1116,1117,1118,1119,1120,1121,1122,1123,1124,1125,1126,1127,1128,1129,1130,1131,1132,1133,1134,1135,1136,1137,1138,1139,1140,1141,1142,1143,1144,1145,1146,1147,1148,1149,1150,1151,1152,1153,1154,1155,1156,1157,1158,1159,1160,1161,1162,1163,1164,1165,1166,1167,1168,1169,1170,1171,1172,1173,1174,1175,1176,1177,1178,1179,1180,1181,1182,1183,1184,1185,1186,1187,1188,1189,1190,1191,1192,1193,1194,1195,1196,1197,1198,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1210,1211,1212,1213,1214,1215,1216,1217,1218,1219,1220,1221,1222,1223,1224,1225,1226,1227,1228,1229,1230,1231,1232,1233,1234,1235,1236,1237,1238,1239,1240,1241,1242,1243,1244,1245,1246,1247,1248,1249,1250,1251,1252,1253,1254,1255,1256,1257,1258,1259,1260,1261,1262,1263,1264,1265,1266,1267,1268,1269,1270,1271,1272,1273,1274,1275,1276,1277,1278,1279,1280,1281,1282,1283,1284,1285,1286,1287,1288,1289,1290,1291,1292,1293,1294,1295,1296,1297,1298,1299,1300,1301,1302,1303,1304,1305,1306,1307,1308,1309,1310,1311,1312,1313,1314,1315,1316,1317,1318,1319,1320,1321,1322,1323,1324,1325,1326,1327,1328,1329,1330,1331,1332,1333,1334,1335,1336,1337,1338,1339,1340,1341,1342,1343,1344,1345,1346,1347,1348,1349,1350,1351,1352,1353,1354,1355,1356,1357,1358,1359,1360,1361,1362,1363,1364,1365,1366,1367,1368,1369,1370,1371,1372,1373,1374,1375,1376,1377,1378,1379,1380,1381,1382,1383,1384,1385,1386,1387,1388,1389,1390,1391,1392,1393,1394,1395,1396,1397,1398,1399,1400,1401,1402,1403,1404,1405,1406,1407,1408,1409,1410,1411,1412,1413,1414,1415,1416,1417,1418,1419,1420,1421,1422,1423,1424,1425,1426,1427,1428,1429,1430,1431,1432,1433,1434,1435,1436,1437,1438,1439,1440,1441,1442,1443,1444,1445,1446,1447,1448,1449,1450,1451,1452,1453,1454,1455,1456,1457,1458,1459,1460,1461,1462,1463,1464,1465,1466,1467,1468,1469,1470,1471,1472,1473,1474,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1485,1486,1487,1488,1489,1490,1491,1492,1493,1494,1495,1496,1497,1498,1499,1500,1501,1502,1503,1504,1505,1506,1507,1508,1509,1510,1511,1512,1513,1514,1515,1516,1517,1518,1519,1520,1521,1522,1523,1524,1525,1526 ) GROUP BY c1;

+------+--------------+------------+-------+---------------+------+---------+------+------+--------------------------+

| id   | select_type  | table      | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |

+------+--------------+------------+-------+---------------+------+---------+------+------+--------------------------+

|    1 | PRIMARY      | t          | index | NULL          | i    | 10      | NULL |    5 | Using where; Using index |

|    2 | MATERIALIZED | <derived3> | ALL   | NULL          | NULL | NULL    | NULL |    2 |                          |

|    3 | DERIVED      | NULL       | NULL  | NULL          | NULL | NULL    | NULL | NULL | No tables used           |

+------+--------------+------------+-------+---------------+------+---------+------+------+--------------------------+

3 rows in set (0.00 sec)

Comment by Igor Babaev [ 2018-11-26 ]

Stephane,
Your problem is definitely not the same as Frank's one: you complain about NOT IN transformation, Frank complains about IN transformation when PARTITION is used.
Honestly, I don't understand what your problem is.

Comment by VAROQUI Stephane [ 2018-11-26 ]

I think IN or NOT IN does not matter , the issue is that the plan can not go from QUERY to SUBQUERY but only from SUBQUERY to QUERY that indeed can give regression , there is no index to go from SUBQUERY to QUERY because the column that make the SUBQUERY is part of a not first position in a composite index .

Comment by Igor Babaev [ 2018-11-26 ]

Stephane,
Did you notice PARTITION BY HASH (`next_id_int`)?

Comment by VAROQUI Stephane [ 2018-11-27 ]

Ok sorry i stop digging, was just curious about the number of operations , that could lead to plan traversal vs full index lookup with covering index

For a 5 rows table
handler_read_rnd | 16284 |
Handler_read_rnd_next | 2046 |

Handler_tmp_write

2044

From the status i can guess the temp table is sorted, would like to know if a hash index instead would not be preferable in such case ?

Comment by Igor Babaev [ 2018-11-27 ]

Frank,
Could you please give us an OUTPUT from EXPLAIN PARTITIONS for your query?

Comment by Frank Sagurna [ 2018-11-28 ]

So, I removed the partitioning, to be sure that this is not the problem.

MariaDB [leitsystem]> show create table zr_te_value;

+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table       | Create Table                                                                                                                                                                                                                                                                             |

+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| zr_te_value | CREATE TABLE `zr_te_value` (

  `zr_art` tinyint(11) NOT NULL,

  `next_id_int` int(11) NOT NULL,

  `ts` int(11) NOT NULL,

  `value` double NOT NULL,

  `quality` tinyint(4) NOT NULL,

  PRIMARY KEY (`zr_art`,`next_id_int`,`ts`),

  KEY `ts` (`ts`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.000 sec)

# Time: 181128  9:15:44

# User@Host:

# Thread_id: 143  Schema: leitsystem  QC_hit: No

# Query_time: 160.526276  Lock_time: 0.000641  Rows_sent: 25  Rows_examined: 213868139

# Rows_affected: 0  Bytes_sent: 1769

# 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      257     137889.22       100.00  0.19    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

SET timestamp=1543392944;

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;

No partition, no int conversion.
Maybe it is the compound index?

Comment by Alice Sherepa [ 2018-11-28 ]

I tested 10.3 35184902db9291ff784 vs 10.2 b26e603aebc0c375751cc1d08 (Debug versions), I can reproduce the difference in execution time with the test case:

CREATE TABLE `t1` ( `z1` tinyint(11) NOT NULL, `id` int NOT NULL, `ts` int NOT NULL, `value` double NOT NULL, `quality` tinyint(4) NOT NULL, PRIMARY KEY (`z1`,`id`,`ts`), KEY `ts` (`ts`)) ENGINE=InnoDB;

insert  into t1 select 12, 32,seq,seq,5 from seq_1_to_100000;

insert  into t1 select 12, 2001,seq,seq,5 from seq_100001_to_100500;

analyze format=json

SELECT id, AVG(quality)

FROM t1

WHERE id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000)#,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1018,1019,1020,1021,1022,1023,1024,1025,1026,1027,1028,1029,1030,1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1043,1044,1045,1046,1047,1048,1049,1050,1051,1052,1053,1054,1055,1056,1057,1058,1059,1060,1061,1062,1063,1064,1065,1066,1067,1068,1069,1070,1071,1072,1073,1074,1075,1076,1077,1078,1079,1080,1081,1082,1083,1084,1085,1086,1087,1088,1089,1090,1091,1092,1093,1094,1095,1096,1097,1098,1099,1100,1101,1102,1103,1104,1105,1106,1107,1108,1109,1110,1111,1112,1113,1114,1115,1116,1117,1118,1119,1120,1121,1122,1123,1124,1125,1126,1127,1128,1129,1130,1131,1132,1133,1134,1135,1136,1137,1138,1139,1140,1141,1142,1143,1144,1145,1146,1147,1148,1149,1150,1151,1152,1153,1154,1155,1156,1157,1158,1159,1160,1161,1162,1163,1164,1165,1166,1167,1168,1169,1170,1171,1172,1173,1174,1175,1176,1177,1178,1179,1180,1181,1182,1183,1184,1185,1186,1187,1188,1189,1190,1191,1192,1193,1194,1195,1196,1197,1198,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1210,1211,1212,1213,1214,1215,1216,1217,1218,1219,1220,1221,1222,1223,1224,1225,1226,1227,1228,1229,1230,1231,1232,1233,1234,1235,1236,1237,1238,1239,1240,1241,1242,1243,1244,1245,1246,1247,1248,1249,1250,1251,1252,1253,1254,1255,1256,1257,1258,1259,1260,1261,1262,1263,1264,1265,1266,1267,1268,1269,1270,1271,1272,1273,1274,1275,1276,1277,1278,1279,1280,1281,1282,1283,1284,1285,1286,1287,1288,1289,1290,1291,1292,1293,1294,1295,1296,1297,1298,1299,1300,1301,1302,1303,1304,1305,1306,1307,1308,1309,1310,1311,1312,1313,1314,1315,1316,1317,1318,1319,1320,1321,1322,1323,1324,1325,1326,1327,1328,1329,1330,1331,1332,1333,1334,1335,1336,1337,1338,1339,1340,1341,1342,1343,1344,1345,1346,1347,1348,1349,1350,1351,1352,1353,1354,1355,1356,1357,1358,1359,1360,1361,1362,1363,1364,1365,1366,1367,1368,1369,1370,1371,1372,1373,1374,1375,1376,1377,1378,1379,1380,1381,1382,1383,1384,1385,1386,1387,1388,1389,1390,1391,1392,1393,1394,1395,1396,1397,1398,1399,1400,1401,1402,1403,1404,1405,1406,1407,1408,1409,1410,1411,1412,1413,1414,1415,1416,1417,1418,1419,1420,1421,1422,1423,1424,1425,1426,1427,1428,1429,1430,1431,1432,1433,1434,1435,1436,1437,1438,1439,1440,1441,1442,1443,1444,1445,1446,1447,1448,1449,1450,1451,1452,1453,1454,1455,1456,1457,1458,1459,1460,1461,1462,1463,1464,1465,1466,1467,1468,1469,1470,1471,1472,1473,1474,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1485,1486,1487,1488,1489,1490,1491,1492,1493,1494,1495,1496,1497,1498,1499,1500)

AND ts >= 54275 AND ts < 80000 AND z1 = 12

GROUP BY id, ts DIV 300

ORDER BY ts;

10.3.12-MariaDB-debug

{  "query_block": {    "select_id": 1,    "r_loops": 1,    "r_total_time_ms": 917.95,    "filesort": {      "sort_key": "t1.ts",      "r_loops": 1,      "r_total_time_ms": 0.1072,      "r_used_priority_queue": false,      "r_output_rows": 87,      "r_buffer_size": "1Kb",      "temporary_table": {        "table": {          "table_name": "<subquery2>",          "access_type": "ALL",          "possible_keys": ["distinct_key"],          "r_loops": 1,          "rows": 1000,          "r_rows": 1000,          "r_total_time_ms": 1.1175,          "filtered": 100,          "r_filtered": 100,          "materialized": {            "unique": 1,            "query_block": {              "select_id": 2,              "table": {                "table_name": "<derived3>",                "access_type": "ALL",                "r_loops": 1,                "rows": 1000,                "r_rows": 1000,                "r_total_time_ms": 0.6444,                "filtered": 100,                "r_filtered": 100,                "materialized": {                  "query_block": {                    "union_result": {                      "table_name": "<unit3>",                      "access_type": "ALL",                      "r_loops": 0,                      "r_rows": null,                      "query_specifications": [                        {                          "query_block": {                            "select_id": 3,                            "table": {                              "message": "No tables used"                            }                          }                        }                      ]                    }                  }                }              }            }          }        },        "table": {          "table_name": "t1",          "access_type": "ref",          "possible_keys": ["PRIMARY", "ts"],          "key": "PRIMARY",          "key_length": "5",          "used_key_parts": ["z1", "id"],          "ref": ["const", "tvc_0._col_1"],          "r_loops": 1000,          "rows": 1,          "r_rows": 100,          "r_total_time_ms": 751.09,          "filtered": 100,          "r_filtered": 25.725,          "attached_condition": "t1.ts >= 54275 and t1.ts < 80000"        }      }    }  }}

10.2.20-MariaDB-debug

{  "query_block": {    "select_id": 1,    "r_loops": 1,    "r_total_time_ms": 372.83,    "filesort": {      "sort_key": "t1.ts",      "r_loops": 1,      "r_total_time_ms": 0.1396,      "r_used_priority_queue": false,      "r_output_rows": 87,      "r_buffer_size": "1Kb",      "temporary_table": {        "table": {          "table_name": "t1",          "access_type": "range",          "possible_keys": ["PRIMARY", "ts"],          "key": "PRIMARY",          "key_length": "9",          "used_key_parts": ["z1", "id", "ts"],          "r_loops": 1,          "rows": 50273,          "r_rows": 25725,          "r_total_time_ms": 212.85,          "filtered": 100,          "r_filtered": 100,          "attached_condition": "t1.z1 = 12 and t1.`id` in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000) and t1.ts >= 54275 and t1.ts < 80000"        }      }    }  }}

If there is no SELECT of AVG(quality):

SELECT id FROM t1

WHERE id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000)#,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1018,1019,1020,1021,1022,1023,1024,1025,1026,1027,1028,1029,1030,1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1043,1044,1045,1046,1047,1048,1049,1050,1051,1052,1053,1054,1055,1056,1057,1058,1059,1060,1061,1062,1063,1064,1065,1066,1067,1068,1069,1070,1071,1072,1073,1074,1075,1076,1077,1078,1079,1080,1081,1082,1083,1084,1085,1086,1087,1088,1089,1090,1091,1092,1093,1094,1095,1096,1097,1098,1099,1100,1101,1102,1103,1104,1105,1106,1107,1108,1109,1110,1111,1112,1113,1114,1115,1116,1117,1118,1119,1120,1121,1122,1123,1124,1125,1126,1127,1128,1129,1130,1131,1132,1133,1134,1135,1136,1137,1138,1139,1140,1141,1142,1143,1144,1145,1146,1147,1148,1149,1150,1151,1152,1153,1154,1155,1156,1157,1158,1159,1160,1161,1162,1163,1164,1165,1166,1167,1168,1169,1170,1171,1172,1173,1174,1175,1176,1177,1178,1179,1180,1181,1182,1183,1184,1185,1186,1187,1188,1189,1190,1191,1192,1193,1194,1195,1196,1197,1198,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1210,1211,1212,1213,1214,1215,1216,1217,1218,1219,1220,1221,1222,1223,1224,1225,1226,1227,1228,1229,1230,1231,1232,1233,1234,1235,1236,1237,1238,1239,1240,1241,1242,1243,1244,1245,1246,1247,1248,1249,1250,1251,1252,1253,1254,1255,1256,1257,1258,1259,1260,1261,1262,1263,1264,1265,1266,1267,1268,1269,1270,1271,1272,1273,1274,1275,1276,1277,1278,1279,1280,1281,1282,1283,1284,1285,1286,1287,1288,1289,1290,1291,1292,1293,1294,1295,1296,1297,1298,1299,1300,1301,1302,1303,1304,1305,1306,1307,1308,1309,1310,1311,1312,1313,1314,1315,1316,1317,1318,1319,1320,1321,1322,1323,1324,1325,1326,1327,1328,1329,1330,1331,1332,1333,1334,1335,1336,1337,1338,1339,1340,1341,1342,1343,1344,1345,1346,1347,1348,1349,1350,1351,1352,1353,1354,1355,1356,1357,1358,1359,1360,1361,1362,1363,1364,1365,1366,1367,1368,1369,1370,1371,1372,1373,1374,1375,1376,1377,1378,1379,1380,1381,1382,1383,1384,1385,1386,1387,1388,1389,1390,1391,1392,1393,1394,1395,1396,1397,1398,1399,1400,1401,1402,1403,1404,1405,1406,1407,1408,1409,1410,1411,1412,1413,1414,1415,1416,1417,1418,1419,1420,1421,1422,1423,1424,1425,1426,1427,1428,1429,1430,1431,1432,1433,1434,1435,1436,1437,1438,1439,1440,1441,1442,1443,1444,1445,1446,1447,1448,1449,1450,1451,1452,1453,1454,1455,1456,1457,1458,1459,1460,1461,1462,1463,1464,1465,1466,1467,1468,1469,1470,1471,1472,1473,1474,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1485,1486,1487,1488,1489,1490,1491,1492,1493,1494,1495,1496,1497,1498,1499,1500)

AND ts >= 54275 AND ts < 80000 AND z1 = 12

GROUP BY id, ts DIV 300 ORDER BY ts;

10.3.12-MariaDB-debug

{  "query_block": {    "select_id": 1,    "r_loops": 1,    "r_total_time_ms": 882.33,    "filesort": {      "sort_key": "t1.ts",      "r_loops": 1,      "r_total_time_ms": 0.1049,      "r_used_priority_queue": false,      "r_output_rows": 87,      "r_buffer_size": "1Kb",      "temporary_table": {        "table": {          "table_name": "<subquery2>",          "access_type": "ALL",          "possible_keys": ["distinct_key"],          "r_loops": 1,          "rows": 1000,          "r_rows": 1000,          "r_total_time_ms": 1.7804,          "filtered": 100,          "r_filtered": 100,          "materialized": {            "unique": 1,            "query_block": {              "select_id": 2,              "table": {                "table_name": "<derived3>",                "access_type": "ALL",                "r_loops": 1,                "rows": 1000,                "r_rows": 1000,                "r_total_time_ms": 0.8423,                "filtered": 100,                "r_filtered": 100,                "materialized": {                  "query_block": {                    "union_result": {                      "table_name": "<unit3>",                      "access_type": "ALL",                      "r_loops": 0,                      "r_rows": null,                      "query_specifications": [                        {                          "query_block": {                            "select_id": 3,                            "table": {                              "message": "No tables used"                            }                          }                        }                      ]                    }                  }                }              }            }          }        },        "table": {          "table_name": "t1",          "access_type": "ref",          "possible_keys": ["PRIMARY", "ts"],          "key": "PRIMARY",          "key_length": "5",          "used_key_parts": ["z1", "id"],          "ref": ["const", "tvc_0._col_1"],          "r_loops": 1000,          "rows": 1,          "r_rows": 100,          "r_total_time_ms": 732.08,          "filtered": 100,          "r_filtered": 25.725,          "attached_condition": "t1.ts >= 54275 and t1.ts < 80000",          "using_index": true        }      }    }  }}

10.2.20-MariaDB-debug

{  "query_block": {    "select_id": 1,    "r_loops": 1,    "r_total_time_ms": 662.55,    "filesort": {      "sort_key": "t1.ts",      "r_loops": 1,      "r_total_time_ms": 0.138,      "r_used_priority_queue": false,      "r_output_rows": 87,      "r_buffer_size": "1Kb",      "temporary_table": {        "table": {          "table_name": "t1",          "access_type": "ref",          "possible_keys": ["PRIMARY", "ts"],          "key": "PRIMARY",          "key_length": "1",          "used_key_parts": ["z1"],          "ref": ["const"],          "r_loops": 1,          "rows": 50273,          "r_rows": 100500,          "r_total_time_ms": 506.26,          "filtered": 100,          "r_filtered": 25.597,          "attached_condition": "t1.z1 <=> 12 and t1.`id` in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000) and t1.ts >= 54275 and t1.ts < 80000",          "using_index": true        }      }    }  }}

Comment by Igor Babaev [ 2018-12-02 ]

Frank,
I need the output from:
1. select count ( * ) from zr_te_value;
2. select count(distinct zr_art) from zr_te_value;
3. select count(distinct zr_art, next_id_int) from zr_te_value;
4. select count( * ) from zr_te_value where zr_art=12;
4. select count(distinct next_id_int) from zr_te_value where zr_art=12;
And how many values from the IN list are in the table when zr_art=12 (or probability of it).

Comment by Frank Sagurna [ 2018-12-03 ]

MariaDB [leitsystem]> select count(*) from zr_te_value;

| 789487402 |

MariaDB [leitsystem]> select count(distinct zr_art) from zr_te_value;

|                      4 |

MariaDB [leitsystem]> select count(distinct zr_art,next_id_int) from zr_te_value;

|                               7903 |

MariaDB [leitsystem]> select count(*) from zr_te_value WHERE zr_art=12;

| 242272197 |

MariaDB [leitsystem]> select count(distinct next_id_int) from zr_te_value WHERE zr_art=12;

|                        1751 |

It is >99% sure, that the values in the IN list are in the zr_te_value table.

Comment by Igor Babaev [ 2018-12-10 ]

Frank,
Your list of values used in IN predicate , is it generated by an application?
If by by any chance it is not generated then you could divide the list into two:<left_part>IN(list1) OR <left_part>IN(list2)
and have the same performance as before.
If it's not a solution for you then please open an MDEV asking to expose for users the system variable in_predicate_conversion_threshold.

Comment by Frank Sagurna [ 2018-12-10 ]

Yes it is generated, but i think it will not be possible in short term to change that.
Please close this ticket here, i am tired of this. I wont report another one.

Comment by jocelyn fournier [ 2019-03-02 ]

Hi Igor,

Any updates on this issue, on perhaps a workaround to disable this optimisation? I've also a case where this kind of queries are not really efficient in 10.3.x (although not that slow).

Thanks!
Jocelyn

Comment by jocelyn fournier [ 2019-03-06 ]

Wonder why the in_predicate_conversion_threshold variable has been moved in debug build only? Ok, it's https://jira.mariadb.org/browse/MDEV-16871

Comment by Igor Babaev [ 2019-03-07 ]

Hi Jocelyn,
We are planning to make in_predicate_conversion_threshold visible for users.

Comment by Igor Babaev [ 2019-03-07 ]

Jocelyn,
We are extremely interested in the test cases when the conversion of IN-predicate into
IN-subsquery causes performance regression.
In the case of this bug entry the most probable cause is that after such conversion the narrow ranges on the second component of the used index has been ignored because after the conversion we have ref access from the temporary table containing the values of the IN- predicate. At the same time the first component is not selective enough. So in this case we need to support ref+range access (see MDEV-4729) and we are planning to do it for 10.5.

We are not planning to ditch this conversion because for many customers it was extremely efficient (showed 10 times better performance in some cases).

Comment by jocelyn fournier [ 2019-03-07 ]

Hi Igor,

In my case, after a more thorough analysis, it's seems it's more the semi-join optimization which is involved:

SELECT `news`.* FROM `news` WHERE (news.publier = 1) AND (news.date_publication < "2019-03-07 19:20:08") AND (news.date_fin > "2019-03-07 19:20:08" OR news.date_fin IS NULL) AND (news.sponsored = 0) AND (news.idnews in ([about 1600 ids]) ) GROUP BY `news`.`idnews` ORDER BY `date_publication` desc LIMIT 5

Execution plan:

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: <derived3>

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 8949

        Extra: Start temporary; Using temporary; Using filesort

*************************** 2. row ***************************

           id: 1

  select_type: PRIMARY

        table: news

         type: eq_ref

possible_keys: PRIMARY,date_publication

          key: PRIMARY

      key_len: 4

          ref: tvc_0._col_1

         rows: 1

        Extra: Using where; End temporary

*************************** 3. row ***************************

           id: 3

  select_type: DERIVED

        table: NULL

         type: NULL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: NULL

        Extra: No tables used

3 rows in set (0.006 sec)

Table struct:

CREATE TABLE `news` (

  `idnews` int(11) NOT NULL AUTO_INCREMENT,

  `titre` varchar(255) CHARACTER SET latin1 DEFAULT NULL,

  `contenu` text CHARACTER SET latin1 DEFAULT NULL,

  `date_publication` timestamp NULL DEFAULT '0000-00-00 00:00:00',

  `date_modification` timestamp NULL DEFAULT '0000-00-00 00:00:00',

  `image` varchar(255) CHARACTER SET latin1 DEFAULT NULL,

  `image_mini` varchar(255) CHARACTER SET latin1 DEFAULT NULL,

  `publier` tinyint(1) DEFAULT NULL,

  `categorie` int(11) DEFAULT NULL,

  `likes` int(11) NOT NULL DEFAULT 0,

  `visites` int(11) NOT NULL DEFAULT 0,

  `retweet` int(11) NOT NULL DEFAULT 0,

  `gplus` int(11) NOT NULL DEFAULT 0,

  `maj_social` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

  `date_fin` timestamp NULL DEFAULT NULL,

  `chapo` text DEFAULT NULL,

  `star_news` tinyint(1) DEFAULT 0,

  `auteur` varchar(255) DEFAULT NULL,

  `newsimport` int(10) NOT NULL,

  `old` varchar(200) NOT NULL,

  `site_origine` varchar(25) NOT NULL,

  `id_origine` int(6) NOT NULL,

  `credits_photo` varchar(255) DEFAULT NULL,

  `iframe_videos` text DEFAULT NULL,

  `zones` varchar(200) DEFAULT NULL,

  `date_affichage` tinyint(3) unsigned DEFAULT 1,

  `partage_affichage` tinyint(3) unsigned DEFAULT 1,

  `sponsored` tinyint(3) unsigned DEFAULT 0,

  `legende_photo` varchar(255) DEFAULT NULL,

  `ordre` int(11) DEFAULT NULL,

  `contenu_amp` text DEFAULT NULL,

  `iframe_videos_amp` text DEFAULT NULL,

  PRIMARY KEY (`idnews`),

  KEY `newsimport` (`newsimport`,`idnews`),

  KEY `date_publication` (`date_publication`)

) ENGINE=InnoDB AUTO_INCREMENT=41981 DEFAULT CHARSET=utf8

Rewriting by the optimiser:

/* select#1 */ select `voltage`.`news`.`idnews` AS `idnews`,`voltage`.`news`.`titre` AS `titre`,`voltage`.`news`.`contenu` AS `contenu`,`voltage`.`news`.`date_publication` AS `date_publication`,`voltage`.`news`.`date_modification` AS `date_modification`,`voltage`.`news`.`image` AS `image`,`voltage`.`news`.`image_mini` AS `image_mini`,`voltage`.`news`.`publier` AS `publier`,`voltage`.`news`.`categorie` AS `categorie`,`voltage`.`news`.`likes` AS `likes`,`voltage`.`news`.`visites` AS `visites`,`voltage`.`news`.`retweet` AS `retweet`,`voltage`.`news`.`gplus` AS `gplus`,`voltage`.`news`.`maj_social` AS `maj_social`,`voltage`.`news`.`date_fin` AS `date_fin`,`voltage`.`news`.`chapo` AS `chapo`,`voltage`.`news`.`star_news` AS `star_news`,`voltage`.`news`.`auteur` AS `auteur`,`voltage`.`news`.`newsimport` AS `newsimport`,`voltage`.`news`.`old` AS `old`,`voltage`.`news`.`site_origine` AS `site_origine`,`voltage`.`news`.`id_origine` AS `id_origine`,`voltage`.`news`.`credits_photo` AS `credits_photo`,`voltage`.`news`.`iframe_videos` AS `iframe_videos`,`voltage`.`news`.`zones` AS `zones`,`voltage`.`news`.`date_affichage` AS `date_affichage`,`voltage`.`news`.`partage_affichage` AS `partage_affichage`,`voltage`.`news`.`sponsored` AS `sponsored`,`voltage`.`news`.`legende_photo` AS `legende_photo`,`voltage`.`news`.`ordre` AS `ordre`,`voltage`.`news`.`contenu_amp` AS `contenu_amp`,`voltage`.`news`.`iframe_videos_amp` AS `iframe_videos_amp` from `voltage`.`news` semi join ((values [...] `tvc_0`) where `voltage`.`news`.`publier` = 1 and `voltage`.`news`.`sponsored` = 0 and `voltage`.`news`.`date_publication` < '2019-03-07 19:20:08' and (`voltage`.`news`.`date_fin` > '2019-03-07 19:20:08' or `voltage`.`news`.`date_fin` is null) and `voltage`.`news`.`idnews` = `tvc_0`.`_col_1` group by `voltage`.`news`.`idnews` order by `voltage`.`news`.`date_publication` desc limit 5

The associated profiling:

SHOW PROFILE FOR QUERY 1;

+--------------------------------+----------+

| Status                         | Duration |

+--------------------------------+----------+

| Starting                       | 0.000194 |

| Waiting for query cache lock   | 0.000032 |

| Init                           | 0.000022 |

| Checking query cache for query | 0.003343 |

| Checking permissions           | 0.000030 |

| Opening tables                 | 0.000044 |

| After opening tables           | 0.000027 |

| System lock                    | 0.000024 |

| Table lock                     | 0.000027 |

| Waiting for query cache lock   | 0.000431 |

| Init                           | 0.001572 |

| Optimizing                     | 0.000904 |

| Statistics                     | 0.000100 |

| Preparing                      | 0.000069 |

| Creating tmp table             | 0.000072 |

| Sorting result                 | 0.000047 |

| Executing                      | 0.000024 |

| Sending data                   | 0.001152 |

| Removing tmp table             | 0.000029 |

| Sending data                   | 0.107823 |

| Creating sort index            | 0.012458 |

| Removing tmp table             | 0.006703 |

| Creating sort index            | 0.000047 |

| End of update loop             | 0.000027 |

| Removing tmp table             | 0.000027 |

| End of update loop             | 0.000031 |

| Query end                      | 0.000023 |

| Commit                         | 0.000022 |

| Closing tables                 | 0.000021 |

| Removing tmp table             | 0.000023 |

| Closing tables                 | 0.000021 |

| Unlocking tables               | 0.000020 |

| Closing tables                 | 0.000030 |

| Starting cleanup               | 0.000021 |

| Freeing items                  | 0.000256 |

| Updating status                | 0.000030 |

| Waiting for query cache lock   | 0.000020 |

| Updating status                | 0.000071 |

| Waiting for query cache lock   | 0.000026 |

| Updating status                | 0.000026 |

| Storing result in query cache  | 0.000035 |

| Reset for next command         | 0.000030 |

+--------------------------------+----------+

42 rows in set (0.000 sec)

I don't know if the SHOW PROFILE is buggy in this case, but it's weird to have 4x Removing tmp table for one Creating tmp table, and 4x Waiting for query cache lock.
I've a lot of concurrent queries of this type, I wonder if this is the overhead of creating a high number of tmp table which is quite inefficient (in my case I have about 1667 elements in the list). It would be interesting to actually enable the IN-predicate optimisation (lower in_predicate_conversion_threshold) to check its behaviour.

Comment by Igor Babaev [ 2019-03-07 ]

Jocelyn,
Could you please provide we with the output of
ANALYZE FORMAT=JSON <your query>
?
(Yes, the conversion counts on the possibility of semi-join optimizations applied after the conversion)

Comment by jocelyn fournier [ 2019-03-07 ]

Output of the Analyze format:

  "query_block": {

    "select_id": 1,

    "r_loops": 1,

    "r_total_time_ms": 125.17,

    "filesort": {

      "sort_key": "news.date_publication desc",

      "r_loops": 1,

      "r_total_time_ms": 11.992,

      "r_limit": 5,

      "r_used_priority_queue": true,

      "r_output_rows": 6,

      "temporary_table": {

        "duplicates_removal": {

          "table": {

            "table_name": "<derived3>",

            "access_type": "ALL",

            "r_loops": 1,

            "rows": 8949,

            "r_rows": 8949,

            "r_total_time_ms": 0.5357,

            "filtered": 100,

            "r_filtered": 100,

            "materialized": {

              "query_block": {

                "union_result": {

                  "table_name": "<unit3>",

                  "access_type": "ALL",

                  "r_loops": 0,

                  "r_rows": null,

                  "query_specifications": [

                      "query_block": {

                        "select_id": 3,

                        "table": {

                          "message": "No tables used"

},

          "table": {

            "table_name": "news",

            "access_type": "eq_ref",

            "possible_keys": ["PRIMARY", "date_publication"],

            "key": "PRIMARY",

            "key_length": "4",

            "used_key_parts": ["idnews"],

            "ref": ["tvc_0._col_1"],

            "r_loops": 8949,

            "rows": 1,

            "r_rows": 1,

            "r_total_time_ms": 28.308,

            "filtered": 100,

            "r_filtered": 99.497,

            "attached_condition": "news.publier = 1 and news.sponsored = 0 and news.date_publication < '2019-03-07 19:20:08' and (news.date_fin > '2019-03-07 19:20:08' or news.date_fin is null) and news.idnews = tvc_0._col_1"

Comment by Igor Babaev [ 2019-03-07 ]

Jocelyn,
What is the output of ANALYZE FORMAT=JSON in 10.2?

Comment by jocelyn fournier [ 2019-03-07 ]

In 10.2 :

  "query_block": {

    "select_id": 1,

    "r_loops": 1,

    "r_total_time_ms": 0.1169,

    "table": {

      "table_name": "news",

      "access_type": "index",

      "possible_keys": ["PRIMARY", "date_publication"],

      "key": "date_publication",

      "key_length": "5",

      "used_key_parts": ["date_publication"],

      "r_loops": 1,

      "rows": 6626,

      "r_rows": 5,

      "r_total_time_ms": 0.0761,

      "filtered": 100,

      "r_filtered": 100,

      "attached_condition": "news.publier = 1 and news.sponsored = 0 and news.date_publication < '2019-03-07 19:20:08' and (news.date_fin > '2019-03-07 19:20:08' or news.date_fin is null) and news.idnews in ([...])"

Explain is trivial:

+------+-------------+-------+-------+--------------------------+------------------+---------+------+------+-------------+

| id   | select_type | table | type  | possible_keys            | key              | key_len | ref  | rows | Extra       |

+------+-------------+-------+-------+--------------------------+------------------+---------+------+------+-------------+

|    1 | SIMPLE      | news  | index | PRIMARY,date_publication | date_publication | 5       | NULL |    5 | Using where |

+------+-------------+-------+-------+--------------------------+------------------+---------+------+------+-------------+

Comment by jocelyn fournier [ 2019-03-07 ]

FYI in 10.3, the query execution takes about 0.18sec vs 0.06 sec in 10.2

Comment by Igor Babaev [ 2019-03-07 ]

Jocelyn,
If your report the problem in a separate MDEV we'll fix it it in the next 10.3 release.
(just copy and paste your last comment that compares times spent in 10.2 and 10.3
and make a reference to MDEV-17795).
Your problem differs from that of MDEV-17795. Apparently it does not make sense to use the conversion when we have ORDER BY + LIMIT supported by an index.

Comment by jocelyn fournier [ 2019-03-07 ]

Thanks Igor, MDEV-18850 opened!

Comment by Igor Babaev [ 2019-03-07 ]

Jocelyn,
Thank you.
BTW your query in 10.2 will be much faster if you use a trick like this
(news.idnews+0 in ([about 1600 ids])
The problem with your query is that optimizer still tries to evaluate the cost of using primary_key for range access and for this it builds huge structures, so the optimization time becomes really substantial. Of course you can prohibit using primary key by a hint.

Comment by jocelyn fournier [ 2019-03-07 ]

Thanks for the trick! Unfortunately, the application code is obfuscated (although I could use SQLProxy to rewrite the query on the fly).
What about lowering eq_range_index_dive_limit? Wouldn't it have the same effect than news.idnews+0?

Comment by Igor Babaev [ 2019-03-07 ]

Jocelin,
lowering eq_range_index_dive_limit will help though partly, because we won't do unneeded dives. Yet the optimizer still will build unneeded structures for range access.

Comment by Vadim [ 2019-05-30 ]

10.4.5-MariaDB-log, the same bug has been encountered.
The simplest query takes 40.473 seconds to complete:

SELECT

	like_categories.like_id,

	like_categories.text_id,

	like_categories.addit_text_id,

	questions.quest_id

FROM

	like_categories

LEFT JOIN questions

    ON ( questions.like_id = like_categories.like_id )

WHERE

	questions.quest_id IN( /* 1700 ids */ )

	AND like_categories.visible = 1

It results: 490 rows in set (40.473 sec)
The version 10.0.37-MariaDB: 490 rows in set (0.057 sec)

This query has been working more than 10 years and now, after upgrade, it stopped.
We found out the reason is the quantity of elements in the body of the IN statement .
You have an error in your explanation as well here:
https://mariadb.com/kb/en/library/conversion-of-big-in-predicates-into-subqueries/

the IN list has more than 1000 elements is wrong; 999 elements is a real number.

Comment by Igor Babaev [ 2019-05-30 ]

Vadim,
I can't say whether it's the same bug or not unless you give me the info about your indexes.
The output of EXPLAIN EXTENDED from 10.2 also would help.

Comment by Vadim [ 2019-05-31 ]

@igor Igor Babaev,
Here's the create table statements of the two tables mentioned above:

CREATE TABLE `like_categories` (

  `like_id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,

  `text_id` mediumint(6) unsigned NOT NULL,

  `addit_text_id` mediumint(6) unsigned NOT NULL,

  `visible` tinyint(1) unsigned NOT NULL DEFAULT '0',

  `test_id` smallint(5) unsigned DEFAULT NULL,

  `search_click_count` int(11) unsigned NOT NULL DEFAULT '0',

  `users_count` int(11) unsigned NOT NULL DEFAULT '0',

  PRIMARY KEY (`like_id`),

  KEY `visible` (`visible`)

) ENGINE=MyISAM AUTO_INCREMENT=15629 DEFAULT CHARSET=utf8;

-- questions

CREATE TABLE `questions` (

  `quest_id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,

  `text_id` mediumint(6) unsigned NOT NULL COMMENT 'this field is also answer_id, it is unique',

  `status` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '1 - common question; 2 - question about info about user, which we dont need during registration; 3 - questions which are needed during registration',

  `quest_points` smallint(4) unsigned NOT NULL DEFAULT '0',

  `advice_text_id` mediumint(6) unsigned NOT NULL DEFAULT '0',

  `like_id` mediumint(6) unsigned NOT NULL DEFAULT '0',

  `answer_pic` mediumtext,

  `answer_pic_female` mediumtext,

  PRIMARY KEY (`quest_id`),

  KEY `like_id` (`like_id`)

) ENGINE=MyISAM AUTO_INCREMENT=14492 DEFAULT CHARSET=utf8mb4;

As you can see by AUTO_INCREMENT, these tables are quite tiny and even full scan should not take 40 seconds to complete in this case.

Here are EXPLAINs for both versions:
10.0.37-MariaDB

1,SIMPLE,q,ALL,"PRIMARY,like_id",NULL,NULL,NULL,13737,12.46,Using where

1,SIMPLE,lc,eq_ref,"PRIMARY,visible",PRIMARY,3,dating.q.like_id,1,100.00,Using where

10.4.5-MariaDB-log

1,PRIMARY,<derived3>,ALL,NULL,NULL,NULL,NULL,1712,100.00,Start temporary

1,PRIMARY,q,eq_ref,"PRIMARY,like_id",PRIMARY,3,tvc_0._col_1,1,100.00,Using index condition; End temporary

1,PRIMARY,lc,eq_ref,"PRIMARY,visible",PRIMARY,3,dating.q.like_id,1,67.68,Using where

3,DERIVED,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,No tables used

Suddenly, we do not have the version 10.2 and cannot provide its output. Currently, 10.0.37-MariaDB is being used as a primary database and 10.4.5-MariaDB-log as a secondary one.

Comment by Igor Babaev [ 2019-06-01 ]

Vadim,
I really don't understand why the execution plan on 10.4 is slow.
Could you please provide us with the output of ANALYZE for your query on 10.4?
Besides, do you use default settings for optimizer switch on 10.4?

Comment by Vadim [ 2019-06-03 ]

@igor

Could you please provide us with the output of ANALYZE for your query on 10.4?

Sure. Here is the output of the the ANALYZE statement on 10.4.5-MariaDB-log

1,PRIMARY,<derived3>,ALL,NULL,NULL,NULL,NULL,1712,1712.00,100.00,100.00,Start temporary

1,PRIMARY,q,eq_ref,"PRIMARY,like_id",PRIMARY,3,tvc_0._col_1,1,1.00,100.00,100.00,Using index condition; End temporary

1,PRIMARY,lc,eq_ref,"PRIMARY,visible",PRIMARY,3,dating.q.like_id,1,1.00,67.68,28.62,Using where

3,DERIVED,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,No tables used

Besides, do you use default settings for optimizer switch on 10.4?

Yes, indeed.

It is difficult to understand why it behaves this way.
As we noticed, sometimes the query is executed very quickly without any reasons and without any changes in it (with more than 2000 elements in the IN body), but sometimes the queries are hanging for minutes and even hours, using all available connections, which results to Too many connections.

We have no idea what produces such a behaviour.
The 10.0.37-MariaDB behaves as expected.

Comment by Igor Babaev [ 2019-06-03 ]

Vadim,
I need an output from ANALYZE FORMAT=JSON to see where the time is spent.
I would appreciate if you could provide me it.
(Just in case: Read about ANALYZE FORMAT=JSON for statements, it's very helpful for analysis of
performance problems).

Comment by Igor Babaev [ 2019-06-16 ]

Vadim,
Your query cannot be affected by this bug, the execution plan chosen for your query is fast.
There might be another bug though that affects your query.

Comment by Slawomir Pryczek [ 2019-07-17 ]

Hello, this is indeed a major issue i'd even mark it as critical because it makes the server so unstable it's impossible to use on production. And this optimization which is causing it, called "Predicate into subqueries" is nowhere near ready for production because it isn't even able to properly use index in the produced subquery which will turn simplest query into nested full table scan on "random" (by optimizer). Not to mention I personally think it's not a very good idea to introduce subqueries into basic queries for optimization, because how many optimization problems they generally have.

Created my own report for this too: https://jira.mariadb.org/browse/MDEV-20083

This issue is so bad that it results in overloaded CPU and we see query times skyrocket by over 100x (10000%) on production. So some queries which took like 2-3 seconds before are taking 5-10 minutes or even MORE(!!). Nothing is serverd and hundreds of connections are waiting for a lock because simple IN can be turned into NFTS, and there's not even a switch to turn this off...

@Vadim
—
It is difficult to understand why it behaves this way.
As we noticed, sometimes the query is executed very quickly without any reasons and without any changes in it (with more than 2000 elements in the IN body), but sometimes the queries are hanging for minutes and even hours, using all available connections, which results to Too many connections.
—

It's totally random and the whole feature is broken. I also see same behaviour. Some queries use indexes or fast hash lookup properly and they're working like in "old" 10.1, for others it turns into full table nested scan. Then 2 minutes later, the same query gets "treated" differently and it takes 100x more time to run. It's insane! It's even more insane this "optimization" isn't providing any performance benefits in rare cases when it's working as expected.

Just because of it, if you have a tiny table with 100k rows and you want to select 10k rows... one BILLION rows needs to be scanned to get the result this way if only the subquery is told by optimizer not to use indexes(!!)

We need to turn this off as soon as possible. I wasn't using 10.3 in production at all, but that's very bad such critical bug wasn't fixed for so long time, because all it needs is to remove the buggy feature. Moreover this feature concept is totally broken, because how complicated it is to implement and how complicated it is to diagnose the issue because how random it is.

Comment by jocelyn fournier [ 2019-10-31 ]

Hi Igor,

FYI I've added a really bad case of wrong semijoin optimization in https://jira.mariadb.org/browse/MDEV-18850 (note it's not assigned anymore to anyone now?)

Thanks,
Jocelyn

Comment by Phil Porada [ 2020-01-30 ]

Is there any information I can provide to help get this fixed in MariaDB 10.3.x? I run a piece of software that's affected by this and the subquery optimizer.

Comment by Igor Babaev [ 2020-01-31 ]

Phil,
What do you mean by 'to get this fixed'?

Comment by Julien Fritsch [ 2023-12-05 ]

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

Generated at Thu Feb 08 08:39:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-17795] Query with long IN(...) list in WHERE about 40 times slower Created: 2018-11-22 Updated: 2023-12-07