Why does replacing EXISTS with (SELECT EXISTS) significantly speed up performance, in spite EXPLAIN returning the same explanation for both?

Question

MySQL (libmysql - mysqlnd 5.0.12-dev - 20150407. I tried to get it updated a few times but I get push back).

When I run this:

SELECT (SELECT EXISTS(select 1 from cadran_item where id_parcelle = parcelle.id_parcelle or id_tige = tige.id_tige))
FROM secteur
JOIN parcelle ON parcelle.id_secteur = secteur.id_secteur AND parcelle.deleted = 0
LEFT JOIN tige ON tige.id_parcelle = parcelle.id_parcelle AND tige.deleted = 0

I get the result immediately, but when I run this:

SELECT EXISTS(select 1 from cadran_item where id_parcelle = parcelle.id_parcelle or id_tige = tige.id_tige)
FROM secteur
JOIN parcelle ON parcelle.id_secteur = secteur.id_secteur AND parcelle.deleted = 0
LEFT JOIN tige ON tige.id_parcelle = parcelle.id_parcelle AND tige.deleted = 0

I get a time-out after 30 seconds. As you can see, the only different is the additional SELECT in the first query.

Doing EXPLAIN returns the same explanation for both queries.

Why does wrapping the EXISTS in a (SELECT ...) significantly speed up the query? Is this a bug in MySQL 5.0.12-dev or is there more to it?

Edit:

As requested in a comment, I executed EXPLAIN format=json for both query. The result is identical, save for query_block.select_list_subqueries.select_id.

Here's the explain for the first query, followed by the explain for the second query:

{
 "query_block": {
   "select_id": 1,
   "cost_info": {
     "query_cost": "77978.34"
   },
   "nested_loop": [
     {
       "table": {
         "table_name": "parcelle",
         "access_type": "ALL",
         "possible_keys": [
           "parcelle_id_secteur_fk"
         ],
         "rows_examined_per_scan": 21606,
         "rows_produced_per_join": 2160,
         "filtered": "10.00",
         "cost_info": {
           "read_cost": "4242.08",
           "eval_cost": "432.12",
           "prefix_cost": "4674.20",
           "data_read_per_join": "8M"
         },
         "used_columns": [
           "id_parcelle",
           "id_secteur",
           "deleted"
         ],
         "attached_condition": "(`asim-dev`.`parcelle`.`deleted` = 0)"
       }
     },
     {
       "table": {
         "table_name": "secteur",
         "access_type": "eq_ref",
         "possible_keys": [
           "PRIMARY"
         ],
         "key": "PRIMARY",
         "used_key_parts": [
           "id_secteur"
         ],
         "key_length": "4",
         "ref": [
           "asim-dev.parcelle.id_secteur"
         ],
         "rows_examined_per_scan": 1,
         "rows_produced_per_join": 2160,
         "filtered": "100.00",
         "using_index": true,
         "cost_info": {
           "read_cost": "2160.60",
           "eval_cost": "432.12",
           "prefix_cost": "7266.92",
           "data_read_per_join": "5M"
         },
         "used_columns": [
           "id_secteur"
         ]
       }
     },
     {
       "table": {
         "table_name": "tige",
         "access_type": "ref",
         "possible_keys": [
           "id_parcelle"
         ],
         "key": "id_parcelle",
         "used_key_parts": [
           "id_parcelle"
         ],
         "key_length": "4",
         "ref": [
           "asim-dev.parcelle.id_parcelle"
         ],
         "rows_examined_per_scan": 27,
         "rows_produced_per_join": 58926,
         "filtered": "100.00",
         "cost_info": {
           "read_cost": "58926.19",
           "eval_cost": "11785.24",
           "prefix_cost": "77978.34",
           "data_read_per_join": "138M"
         },
         "used_columns": [
           "id_tige",
           "id_parcelle",
           "deleted"
         ],
         "attached_condition": "<if>(is_not_null_compl(tige), (`asim-dev`.`tige`.`deleted` = 0), true)"
       }
     }
   ],
   "select_list_subqueries": [
     {
       "dependent": true,
       "cacheable": false,
       "query_block": {
         "select_id": 3,
         "cost_info": {
           "query_cost": "351.60"
         },
         "table": {
           "table_name": "cadran_item",
           "access_type": "ALL",
           "possible_keys": [
             "id_tige",
             "id_parcelle"
           ],
           "rows_examined_per_scan": 1718,
           "rows_produced_per_join": 326,
           "filtered": "19.00",
           "range_checked_for_each_record": "index map: 0x6",
           "cost_info": {
             "read_cost": "8.00",
             "eval_cost": "65.28",
             "prefix_cost": "351.60",
             "data_read_per_join": "43K"
           },
           "used_columns": [
             "id_parcelle",
             "id_tige"
           ]
         }
       }
     }
   ]
 }
}
{
 "query_block": {
   "select_id": 1,
   "cost_info": {
     "query_cost": "77978.34"
   },
   "nested_loop": [
     {
       "table": {
         "table_name": "parcelle",
         "access_type": "ALL",
         "possible_keys": [
           "parcelle_id_secteur_fk"
         ],
         "rows_examined_per_scan": 21606,
         "rows_produced_per_join": 2160,
         "filtered": "10.00",
         "cost_info": {
           "read_cost": "4242.08",
           "eval_cost": "432.12",
           "prefix_cost": "4674.20",
           "data_read_per_join": "8M"
         },
         "used_columns": [
           "id_parcelle",
           "id_secteur",
           "deleted"
         ],
         "attached_condition": "(asim-dev.parcelle.deleted = 0)"
       }
     },
     {
       "table": {
         "table_name": "secteur",
         "access_type": "eq_ref",
         "possible_keys": [
           "PRIMARY"
         ],
         "key": "PRIMARY",
         "used_key_parts": [
           "id_secteur"
         ],
         "key_length": "4",
         "ref": [
           "asim-dev.parcelle.id_secteur"
         ],
         "rows_examined_per_scan": 1,
         "rows_produced_per_join": 2160,
         "filtered": "100.00",
         "using_index": true,
         "cost_info": {
           "read_cost": "2160.60",
           "eval_cost": "432.12",
           "prefix_cost": "7266.92",
           "data_read_per_join": "5M"
         },
         "used_columns": [
           "id_secteur"
         ]
       }
     },
     {
       "table": {
         "table_name": "tige",
         "access_type": "ref",
         "possible_keys": [
           "id_parcelle"
         ],
         "key": "id_parcelle",
         "used_key_parts": [
           "id_parcelle"
         ],
         "key_length": "4",
         "ref": [
           "asim-dev.parcelle.id_parcelle"
         ],
         "rows_examined_per_scan": 27,
         "rows_produced_per_join": 58926,
         "filtered": "100.00",
         "cost_info": {
           "read_cost": "58926.19",
           "eval_cost": "11785.24",
           "prefix_cost": "77978.34",
           "data_read_per_join": "138M"
         },
         "used_columns": [
           "id_tige",
           "id_parcelle",
           "deleted"
         ],
         "attached_condition": "<if>(is_not_null_compl(tige), (asim-dev.tige.deleted = 0), true)"
       }
     }
   ],
   "select_list_subqueries": [
     {
       "dependent": true,
       "cacheable": false,
       "query_block": {
         "select_id": 2,
         "cost_info": {
           "query_cost": "351.60"
         },
         "table": {
           "table_name": "cadran_item",
           "access_type": "ALL",
           "possible_keys": [
             "id_tige",
             "id_parcelle"
           ],
           "rows_examined_per_scan": 1718,
           "rows_produced_per_join": 326,
           "filtered": "19.00",
           "range_checked_for_each_record": "index map: 0x6",
           "cost_info": {
             "read_cost": "8.00",
             "eval_cost": "65.28",
             "prefix_cost": "351.60",
             "data_read_per_join": "43K"
           },
           "used_columns": [
             "id_parcelle",
             "id_tige"
           ]
         }
       }
     }
   ]
 }
}

score 1 · Answer 1 · answered Mar 13 '25 at 19:50

The reason does not have to do with the SQL itself by how MySQL evaluates the query. MySQL tends to perform transformations of subqueries and eliminate steps so that MySQL can skip doing full table scans and index scans.

I wrote about this 14 years ago in my answer to Problem with MySQL subquery. I am referring to this old post of mine because you are using MySQL 5.0.

Since you buried the SELECT EXISTS within a SELECT, the SELECT EXISTS experienced hidden optimizations that avoided have to do any kind of scanning within a subquery. Older versions of MySQL has the optimizer hidden from the public.

Later versions of MySQL introduced optimizer switches DB Developers can change. This allows you to change the behavior of how SELECT EXISTS is evaluated. Please see the MySQL Documentation on how the optimizer switch can do this in current MySQL Versions. Unfortunately, you cannot change it for MySQL 5.0.

Please don't be surprised that the EXPLAIN is identical for both queries. The query rewrite you did (burying SELECT EXISTS in SELECT) changed the behavior. If the result is the same just faster, JUST GO WITH IT !!!

Why does replacing EXISTS with (SELECT EXISTS) significantly speed up performance, in spite EXPLAIN returning the same explanation for both?

1 Answers1