Lena Startseva added a comment - 2 days ago - edited psergei , I checked on v11.8 - the bug is not reproduced. Also it does not reproduced on v11.4, but still presents in v10.11 (before "analyze table deployment, host;", after "analyze ..." query plan looks good) Results v11.8 MariaDB [cs0275577]> explain SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1; + ------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+ | 1 | SIMPLE | c | index | cluster_fk_deployment | PRIMARY | 767 | NULL | 1 | | | 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 767 | cs0275577.c.deployment_id | 1 | | + ------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+ 2 rows in set (0,001 sec)   MariaDB [cs0275577]> explain SELECT d.id, d.dname, c.id, c.cname, c.state FROM host c straight_join deployment d on d.id=c.deployment_id order by c.id limit 1; + ------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+ | 1 | SIMPLE | c | index | cluster_fk_deployment | PRIMARY | 767 | NULL | 1 | | | 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 767 | cs0275577.c.deployment_id | 1 | | + ------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+ 2 rows in set (0,000 sec)   MariaDB [cs0275577]> SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1; + --------------------------------------+------------+--------------------------------------+------------+---------+ | id | dname | id | cname | state | + --------------------------------------+------------+--------------------------------------+------------+---------+ | 4f8cc10b-83ca-41d3-b5b4-9cc74e1292da | depl-73221 | 00000122-e6b5-499d-993f-8052a9272cf3 | cluster-14 | RUNNING | + --------------------------------------+------------+--------------------------------------+------------+---------+ 1 row in set (0,008 sec)   MariaDB [cs0275577]> SELECT d.id, d.dname, c.id, c.cname, c.state FROM host c straight_join deployment d on d.id=c.deployment_id order by c.id limit 1; + --------------------------------------+------------+--------------------------------------+------------+---------+ | id | dname | id | cname | state | + --------------------------------------+------------+--------------------------------------+------------+---------+ | 4f8cc10b-83ca-41d3-b5b4-9cc74e1292da | depl-73221 | 00000122-e6b5-499d-993f-8052a9272cf3 | cluster-14 | RUNNING | + --------------------------------------+------------+--------------------------------------+------------+---------+ 1 row in set (0,001 sec)   MariaDB [cs0275577]> ANALYZE FORMAT=JSON SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1; | { "query_optimization" : { "r_total_time_ms" : 0.169512799 }, "query_block" : { "select_id" : 1, "cost" : 1569.617368, "r_loops" : 1, "r_total_time_ms" : 0.060071446, "nested_loop" : [ { "table" : { "table_name" : "c" , "access_type" : "index" , "possible_keys" : [ "cluster_fk_deployment" ], "key" : "PRIMARY" , "key_length" : "767" , "used_key_parts" : [ "id" ], "loops" : 1, "r_loops" : 1, "rows" : 1472081, "r_rows" : 1, "cost" : 252.3230302, "r_table_time_ms" : 0.019777489, "r_other_time_ms" : 0.003055806, "r_engine_stats" : { "pages_accessed" : 3 }, "filtered" : 100, "r_total_filtered" : 100, "r_filtered" : 100 } }, { "table" : { "table_name" : "d" , "access_type" : "eq_ref" , "possible_keys" : [ "PRIMARY" ], "key" : "PRIMARY" , "key_length" : "767" , "used_key_parts" : [ "id" ], "ref" : [ "cs0275577.c.deployment_id" ], "loops" : 1472081, "r_loops" : 1, "rows" : 1, "r_rows" : 1, "cost" : 1317.294338, "r_table_time_ms" : 0.018433127, "r_other_time_ms" : 0.010711971, "r_engine_stats" : { "pages_accessed" : 3 }, "filtered" : 100, "r_total_filtered" : 100, "r_filtered" : 100 } } ] } } |   MariaDB [cs0275577]> ANALYZE FORMAT=JSON SELECT d.id, d.dname, c.id, c.cname, c.state FROM host c straight_join deployment d on d.id=c.deployment_id order by c.id limit 1; | { "query_optimization" : { "r_total_time_ms" : 0.150781192 }, "query_block" : { "select_id" : 1, "cost" : 1569.617368, "r_loops" : 1, "r_total_time_ms" : 0.057621024, "nested_loop" : [ { "table" : { "table_name" : "c" , "access_type" : "index" , "possible_keys" : [ "cluster_fk_deployment" ], "key" : "PRIMARY" , "key_length" : "767" , "used_key_parts" : [ "id" ], "loops" : 1, "r_loops" : 1, "rows" : 1472081, "r_rows" : 1, "cost" : 252.3230302, "r_table_time_ms" : 0.019692038, "r_other_time_ms" : 0.003024915, "r_engine_stats" : { "pages_accessed" : 3 }, "filtered" : 100, "r_total_filtered" : 100, "r_filtered" : 100 } }, { "table" : { "table_name" : "d" , "access_type" : "eq_ref" , "possible_keys" : [ "PRIMARY" ], "key" : "PRIMARY" , "key_length" : "767" , "used_key_parts" : [ "id" ], "ref" : [ "cs0275577.c.deployment_id" ], "loops" : 1472081, "r_loops" : 1, "rows" : 1, "r_rows" : 1, "cost" : 1317.294338, "r_table_time_ms" : 0.016906628, "r_other_time_ms" : 0.009813322, "r_engine_stats" : { "pages_accessed" : 3 }, "filtered" : 100, "r_total_filtered" : 100, "r_filtered" : 100 } } ] } } | set optimizer_join_limit_pref_ratio=100; MariaDB [cs0275577]> ANALYZE FORMAT=JSON SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1; | { "query_optimization" : { "r_total_time_ms" : 0.165511406 }, "query_block" : { "select_id" : 1, "cost" : 1569.617368, "r_loops" : 1, "r_total_time_ms" : 0.069949359, "nested_loop" : [ { "table" : { "table_name" : "c" , "access_type" : "index" , "possible_keys" : [ "cluster_fk_deployment" ], "key" : "PRIMARY" , "key_length" : "767" , "used_key_parts" : [ "id" ], "loops" : 1, "r_loops" : 1, "rows" : 1472081, "r_rows" : 1, "cost" : 252.3230302, "r_table_time_ms" : 0.020975019, "r_other_time_ms" : 0.003153294, "r_engine_stats" : { "pages_accessed" : 3 }, "filtered" : 100, "r_total_filtered" : 100, "r_filtered" : 100 } }, { "table" : { "table_name" : "d" , "access_type" : "eq_ref" , "possible_keys" : [ "PRIMARY" ], "key" : "PRIMARY" , "key_length" : "767" , "used_key_parts" : [ "id" ], "ref" : [ "cs0275577.c.deployment_id" ], "loops" : 1472081, "r_loops" : 1, "rows" : 1, "r_rows" : 1, "cost" : 1317.294338, "r_table_time_ms" : 0.026865981, "r_other_time_ms" : 0.010520206, "r_engine_stats" : { "pages_accessed" : 3 }, "filtered" : 100, "r_total_filtered" : 100, "r_filtered" : 100 } } ] } } |   MariaDB [cs0275577]> ANALYZE FORMAT=JSON SELECT d.id, d.dname, c.id, c.cname, c.state FROM host c straight_join deployment d on d.id=c.deployment_id order by c.id limit 1; | { "query_optimization" : { "r_total_time_ms" : 0.1769439 }, "query_block" : { "select_id" : 1, "cost" : 0.26786218, "r_loops" : 1, "r_total_time_ms" : 0.060950838, "nested_loop" : [ { "table" : { "table_name" : "c" , "access_type" : "index" , "possible_keys" : [ "cluster_fk_deployment" ], "key" : "PRIMARY" , "key_length" : "767" , "used_key_parts" : [ "id" ], "loops" : 1, "r_loops" : 1, "rows" : 1472081, "r_rows" : 1, "cost" : 252.3230302, "r_table_time_ms" : 0.020546957, "r_other_time_ms" : 0.003483467, "r_engine_stats" : { "pages_accessed" : 3 }, "filtered" : 100, "r_total_filtered" : 100, "r_filtered" : 100 } }, { "table" : { "table_name" : "d" , "access_type" : "eq_ref" , "possible_keys" : [ "PRIMARY" ], "key" : "PRIMARY" , "key_length" : "767" , "used_key_parts" : [ "id" ], "ref" : [ "cs0275577.c.deployment_id" ], "loops" : 1472081, "r_loops" : 1, "rows" : 1, "r_rows" : 1, "cost" : 1317.294338, "r_table_time_ms" : 0.018015095, "r_other_time_ms" : 0.010125442, "r_engine_stats" : { "pages_accessed" : 3 }, "filtered" : 100, "r_total_filtered" : 100, "r_filtered" : 100 } } ] } } | Results v10.11 MariaDB [cs0275577]> explain SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1; + ------+-------------+-------+------+-----------------------+-----------------------+---------+----------------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ------+-------------+-------+------+-----------------------+-----------------------+---------+----------------+-------+---------------------------------+ | 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 97335 | Using temporary ; Using filesort | | 1 | SIMPLE | c | ref | cluster_fk_deployment | cluster_fk_deployment | 767 | cs0275577.d.id | 7 | | + ------+-------------+-------+------+-----------------------+-----------------------+---------+----------------+-------+---------------------------------+ 2 rows in set (0,001 sec)   MariaDB [cs0275577]> explain SELECT d.id, d.dname, c.id, c.cname, c.state FROM host c straight_join deployment d on d.id=c.deployment_id order by c.id limit 1; + ------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+ | 1 | SIMPLE | c | index | cluster_fk_deployment | PRIMARY | 767 | NULL | 1 | | | 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 767 | cs0275577.c.deployment_id | 1 | | + ------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+ 2 rows in set (0,000 sec)   MariaDB [cs0275577]> SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1; + --------------------------------------+------------+--------------------------------------+------------+---------+ | id | dname | id | cname | state | + --------------------------------------+------------+--------------------------------------+------------+---------+ | 4f8cc10b-83ca-41d3-b5b4-9cc74e1292da | depl-73221 | 00000122-e6b5-499d-993f-8052a9272cf3 | cluster-14 | RUNNING | + --------------------------------------+------------+--------------------------------------+------------+---------+ 1 row in set (2 min 22,434 sec)   MariaDB [cs0275577]> SELECT d.id, d.dname, c.id, c.cname, c.state FROM host c straight_join deployment d on d.id=c.deployment_id order by c.id limit 1; + --------------------------------------+------------+--------------------------------------+------------+---------+ | id | dname | id | cname | state | + --------------------------------------+------------+--------------------------------------+------------+---------+ | 4f8cc10b-83ca-41d3-b5b4-9cc74e1292da | depl-73221 | 00000122-e6b5-499d-993f-8052a9272cf3 | cluster-14 | RUNNING | + --------------------------------------+------------+--------------------------------------+------------+---------+ 1 row in set (0,008 sec)   MariaDB [cs0275577]> ANALYZE FORMAT=JSON SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1; | { "query_optimization" : { "r_total_time_ms" : 0.180508306 }, "query_block" : { "select_id" : 1, "r_loops" : 1, "r_total_time_ms" : 141735.7681, "filesort" : { "sort_key" : "c.`id`" , "r_loops" : 1, "r_total_time_ms" : 353.6436388, "r_limit" : 1, "r_used_priority_queue" : true , "r_output_rows" : 2, "r_sort_mode" : "sort_key,rowid" , "temporary_table" : { "nested_loop" : [ { "table" : { "table_name" : "d" , "access_type" : "ALL" , "possible_keys" : [ "PRIMARY" ], "r_loops" : 1, "rows" : 97335, "r_rows" : 98048, "r_table_time_ms" : 132.0768465, "r_other_time_ms" : 15.46082875, "r_engine_stats" : { "pages_accessed" : 723, "pages_read_count" : 722, "pages_read_time_ms" : 74.16288211 }, "filtered" : 100, "r_filtered" : 100 } }, { "table" : { "table_name" : "c" , "access_type" : "ref" , "possible_keys" : [ "cluster_fk_deployment" ], "key" : "cluster_fk_deployment" , "key_length" : "767" , "used_key_parts" : [ "deployment_id" ], "ref" : [ "cs0275577.d.id" ], "r_loops" : 98048, "rows" : 7, "r_rows" : 15.01388096, "r_table_time_ms" : 140220.5465, "r_other_time_ms" : 996.1884942, "r_engine_stats" : { "pages_accessed" : 4733136, "pages_read_count" : 1449246, "pages_read_time_ms" : 134750.4458 }, "filtered" : 100, "r_filtered" : 100 } } ] } } } } |   MariaDB [cs0275577]> ANALYZE FORMAT=JSON SELECT d.id, d.dname, c.id, c.cname, c.state FROM host c straight_join deployment d on d.id=c.deployment_id order by c.id limit 1; | { "query_optimization" : { "r_total_time_ms" : 0.150586393 }, "query_block" : { "select_id" : 1, "r_loops" : 1, "r_total_time_ms" : 6.911347226, "nested_loop" : [ { "table" : { "table_name" : "c" , "access_type" : "index" , "possible_keys" : [ "cluster_fk_deployment" ], "key" : "PRIMARY" , "key_length" : "767" , "used_key_parts" : [ "id" ], "r_loops" : 1, "rows" : 1458535, "r_rows" : 1, "r_table_time_ms" : 6.309494687, "r_other_time_ms" : 0.005313732, "r_engine_stats" : { "pages_accessed" : 3, "pages_read_count" : 1, "pages_read_time_ms" : 6.21831851 }, "filtered" : 100, "r_filtered" : 100 } }, { "table" : { "table_name" : "d" , "access_type" : "eq_ref" , "possible_keys" : [ "PRIMARY" ], "key" : "PRIMARY" , "key_length" : "767" , "used_key_parts" : [ "id" ], "ref" : [ "cs0275577.c.deployment_id" ], "r_loops" : 1, "rows" : 1, "r_rows" : 1, "r_table_time_ms" : 0.572048665, "r_other_time_ms" : 0.01546543, "r_engine_stats" : { "pages_accessed" : 3, "pages_read_count" : 3, "pages_read_time_ms" : 0.541305301 }, "filtered" : 100, "r_filtered" : 100 } } ] } } | After analyze table deployment, host; MariaDB [cs0275577]> explain SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1; + ------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+ | 1 | SIMPLE | c | index | cluster_fk_deployment | PRIMARY | 767 | NULL | 1 | | | 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 767 | cs0275577.c.deployment_id | 1 | | + ------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+ 2 rows in set (0,000 sec)   MariaDB [cs0275577]> explain SELECT d.id, d.dname, c.id, c.cname, c.state FROM host c straight_join deployment d on d.id=c.deployment_id order by c.id limit 1; + ------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+ | 1 | SIMPLE | c | index | cluster_fk_deployment | PRIMARY | 767 | NULL | 1 | | | 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 767 | cs0275577.c.deployment_id | 1 | | + ------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+ 2 rows in set (0,000 sec)   MariaDB [cs0275577]> SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1; + --------------------------------------+------------+--------------------------------------+------------+---------+ | id | dname | id | cname | state | + --------------------------------------+------------+--------------------------------------+------------+---------+ | 4f8cc10b-83ca-41d3-b5b4-9cc74e1292da | depl-73221 | 00000122-e6b5-499d-993f-8052a9272cf3 | cluster-14 | RUNNING | + --------------------------------------+------------+--------------------------------------+------------+---------+ 1 row in set (0,008 sec)   MariaDB [cs0275577]> SELECT d.id, d.dname, c.id, c.cname, c.state FROM host c straight_join deployment d on d.id=c.deployment_id order by c.id limit 1; + --------------------------------------+------------+--------------------------------------+------------+---------+ | id | dname | id | cname | state | + --------------------------------------+------------+--------------------------------------+------------+---------+ | 4f8cc10b-83ca-41d3-b5b4-9cc74e1292da | depl-73221 | 00000122-e6b5-499d-993f-8052a9272cf3 | cluster-14 | RUNNING | + --------------------------------------+------------+--------------------------------------+------------+---------+ 1 row in set (0,001 sec)   MariaDB [cs0275577]> ANALYZE FORMAT=JSON SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1; | { "query_optimization" : { "r_total_time_ms" : 0.180505094 }, "query_block" : { "select_id" : 1, "r_loops" : 1, "r_total_time_ms" : 0.063952684, "nested_loop" : [ { "table" : { "table_name" : "c" , "access_type" : "index" , "possible_keys" : [ "cluster_fk_deployment" ], "key" : "PRIMARY" , "key_length" : "767" , "used_key_parts" : [ "id" ], "r_loops" : 1, "rows" : 1472081, "r_rows" : 1, "r_table_time_ms" : 0.022598317, "r_other_time_ms" : 0.003997645, "r_engine_stats" : { "pages_accessed" : 3 }, "filtered" : 100, "r_filtered" : 100 } }, { "table" : { "table_name" : "d" , "access_type" : "eq_ref" , "possible_keys" : [ "PRIMARY" ], "key" : "PRIMARY" , "key_length" : "767" , "used_key_parts" : [ "id" ], "ref" : [ "cs0275577.c.deployment_id" ], "r_loops" : 1, "rows" : 1, "r_rows" : 1, "r_table_time_ms" : 0.017576068, "r_other_time_ms" : 0.010991617, "r_engine_stats" : { "pages_accessed" : 3 }, "filtered" : 100, "r_filtered" : 100 } } ] } } |   MariaDB [cs0275577]> ANALYZE FORMAT=JSON SELECT d.id, d.dname, c.id, c.cname, c.state FROM host c straight_join deployment d on d.id=c.deployment_id order by c.id limit 1; | { "query_optimization" : { "r_total_time_ms" : 0.156491922 }, "query_block" : { "select_id" : 1, "r_loops" : 1, "r_total_time_ms" : 0.073268076, "nested_loop" : [ { "table" : { "table_name" : "c" , "access_type" : "index" , "possible_keys" : [ "cluster_fk_deployment" ], "key" : "PRIMARY" , "key_length" : "767" , "used_key_parts" : [ "id" ], "r_loops" : 1, "rows" : 1472081, "r_rows" : 1, "r_table_time_ms" : 0.021794131, "r_other_time_ms" : 0.004274272, "r_engine_stats" : { "pages_accessed" : 3 }, "filtered" : 100, "r_filtered" : 100 } }, { "table" : { "table_name" : "d" , "access_type" : "eq_ref" , "possible_keys" : [ "PRIMARY" ], "key" : "PRIMARY" , "key_length" : "767" , "used_key_parts" : [ "id" ], "ref" : [ "cs0275577.c.deployment_id" ], "r_loops" : 1, "rows" : 1, "r_rows" : 1, "r_table_time_ms" : 0.017481717, "r_other_time_ms" : 0.020490892, "r_engine_stats" : { "pages_accessed" : 3 }, "filtered" : 100, "r_filtered" : 100 } } ] } } |

Sergei Petrunia added a comment - 2 days ago Reading the above comment: Results v11.8 explain SELECT : join order c,d explain SELECT c STRAIGHT JOIN d: join order c,d SELECT times differ but that might be a fluke... ANALYZE SELECT and ANALYZE SELECT STRAIGHT JOIN outputs have only minor differences. With set optimizer_join_limit_pref_ratio=100 , ANALYZE output has the same costs/estimates/etc, except the whole query cost which is much smaller: "query_block" : { "select_id" : 1, "cost" : 0.26786218, // this was 1500 .   "nested_loop" : [ { "table" : { "table_name" : "c" , "cost" : 252.3230302, } }, { "table" : { "table_name" : "d" , "cost" : 1317.294338, Makes sense although looks counter-intuitive at the first glance. Results v10.11 explain SELECT : join order d,c explain SELECT c STRAIGHT JOIN d: join order c,d SELECTs : query time much higher without STRAIGHT JOIN

Sergei Petrunia added a comment - 2 days ago - edited MDEV-35300 is about producing a more intuitive EXPLAIN output (and just that, no change in optimization)

Sergei Petrunia added a comment - 2 days ago Ok, so the take-aways are: This issue has shown two problems: A) poor estimates and B) lack of ORDER BY LIMIT + join optimization. To fix A) make sure to run ANALYZE TABLE. To fix B, https://mariadb.com/kb/en/optimizer_join_limit_pref_ratio-optimization/ ( MDEV-34720 ) is available as a workaround now. One needs to manually enable it. There is a task, MDEV-8306 , to solve this automatically in the future.

Sergei Petrunia added a comment - 2 days ago Duplicate of MDEV-34720 , MDEV-8306 .