PDO binding performance tanks at certain amount of subselects-CodePudding

We switched from MariaDB to Mysql 8.0.30 last week and noticed a query that used to take around 300ms now runs for 32 seconds. Debugging has shown that it has to do with the amount of bindings in the prepared statement and that it only shows up since MySQL version 8.0.22, before that on version 8.0.21 the problem did not exist. Changelog says that they changed the way prepared statements are executed, but I can't relate it to what's happening.

If I remove any of the 4 main exists() sub selects, the query executes in around 300ms again. It does not matter which one.

Inserting the values myself and executing the query directly on the query console results in the same ~300ms execution time.

Setting PDO's ATTR_EMULATE_PREPARES to true resolves the issue, but iirc it is discouraged for security reasons.

This also seems to at least temporarily solve the issue but performance is worse than 300ms

mysqlcheck -uroot -p -o --all-databases

For context: These are shopping products that were automatically generated iterating through 4 different attributes and their values ("generator_conditions"). This is the query generated by Laravel 9 on PHP 8.1.1 to find products that match certain generator_condition_values:

select *
from `products`
where exists(select *
             from `products` as `laravel_reserved_0`
             where `laravel_reserved_0`.`id` = `products`.`parent_id` and `uuid` = ? and
                 exists(select *
                        from `categories`
                                 inner join `categories_product` on `categories`.`id` = `category_product`.`category_id`
                        where `laravel_reserved_0`.`id` = `category_product`.`product_id` and `uuid` = ?) and `laravel_reserved_0`.`deleted_at` is null) and
    exists(select *
           from `generator_condition_values`
                    inner join `generator_condition_value_product` on `generator_condition_values`.`id` = `generator_condition_value_product`.`generator_condition_value_id`
           where `products`.`id` = `generator_condition_value_product`.`product_id` and
               exists(select * from `generator_conditions` where `generator_condition_values`.`generator_condition_id` = `generator_conditions`.`id` and `name` = ?) and `min_value` <= ? and
               (`max_value` >= ? or `max_value` is null)) and
    exists(select *
           from `generator_condition_values`
                    inner join `generator_condition_value_product` on `generator_condition_values`.`id` = `generator_condition_value_product`.`generator_condition_value_id`
           where `products`.`id` = `generator_condition_value_product`.`product_id` and
               exists(select * from `generator_conditions` where `generator_condition_values`.`generator_condition_id` = `generator_conditions`.`id` and `name` = ?) and `min_value` <= ? and
               (`max_value` >= ? or `max_value` is null)) and
    exists(select *
           from `generator_condition_values`
                    inner join `generator_condition_value_product` on `generator_condition_values`.`id` = `generator_condition_value_product`.`generator_condition_value_id`
           where `products`.`id` = `generator_condition_value_product`.`product_id` and
               exists(select * from `generator_conditions` where `generator_condition_values`.`generator_condition_id` = `generator_conditions`.`id` and `name` = ?) and `single_value` = ?) and
    exists(select *
           from `generator_condition_values`
                    inner join `generator_condition_value_product` on `generator_condition_values`.`id` = `generator_condition_value_product`.`generator_condition_value_id`
           where `products`.`id` = `generator_condition_value_product`.`product_id` and
               exists(select * from `generator_conditions` where `generator_condition_values`.`generator_condition_id` = `generator_conditions`.`id` and `name` = ?) and `single_value` = ?)

Bindings:

$bindings = [
    'f607e02a-4003-4852-a72c-42632a5d53a4',
    '5b863422-7b7f-4d34-9077-c2712555fa4d',
    'device_price',
    '1700',
    '1700',
    'device_age',
    0,
    0,
    'is_refurbished',
    '0',
    'is_commercial',
    '0',
];

Any idea why just a few more bindings completely ruin performance? If the actual table structure is needed for context please let me know and I'll add it.

Thanks!

CodePudding user response：

The number of bindings has little, if any, impact on the speed of the query. What matters is the complexity. You can demonstrate this by filling in reasonable values and timing the bound query.

That query is quite complex. FOr each row in products, it does a lookup in 5 other tables. Each of those lookups does another lookup in another table.

Try to rewrite the whole query as the JOIN (or probably LEFT JOIN) of several (6? 11?) tables.

Also, consider adding these indexes

category_product:  INDEX(uuid, product_id, category_id)
products:  INDEX(uuid, parent_id)
generator_condition_values:  INDEX(generator_condition_id)
generator_condition_value_product:  INDEX(product_id, generator_condition_value_id)
generator_conditions:  INDEX(name)

Some of my index suggestions may not work -- since I cannot tell what tables uuid and name are in.

(`max_value` >= ?  or  `max_value` is null)

-- ORs are hard to optimize; see if you can get rid of the test for NULL.

For further discussion, please provide EXPLAIN SELECT ... and SHOW CREATE TABLE.

CodePudding user response：

RemizZ and i worked together on the problem two days before. I may add a few additional information.

MySQL-Version 8.0.24 executed the same query in ~2 s
MySQL-Version 8.0.25 - 8.0.30 has the same behavior as RemizZ described
MariaDb execution time ~20 ms
PostgreSQL execution time ~10 ms

There is no special clause which slowed down the query. It does not matter which nested sub select is removed. The query gets faster if you remove 1...n prepared statements and insert plain SQL. The amount of execution time is reduced also in this process. The query complexity wont decrease in this test.

Explain result:

[
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "laravel_reserved_0",
    "partitions": null,
    "type": "ref",
    "possible_keys": "PRIMARY,products_uuid_index",
    "key": "products_uuid_index",
    "key_len": "144",
    "ref": "const",
    "rows": 1,
    "filtered": 10,
    "Extra": "Using where; Start temporary"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "categories",
    "partitions": null,
    "type": "ref",
    "possible_keys": "PRIMARY,categories_uuid_index",
    "key": "categories_uuid_index",
    "key_len": "144",
    "ref": "const",
    "rows": 1,
    "filtered": 100,
    "Extra": "Using index"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "category_product",
    "partitions": null,
    "type": "eq_ref",
    "possible_keys": "PRIMARY,category_product_category_id_index,category_product_product_id_index",
    "key": "PRIMARY",
    "key_len": "16",
    "ref": "hyve_insurance.categories.id,hyve_insurance.laravel_reserved_0.id",
    "rows": 1,
    "filtered": 100,
    "Extra": "Using index"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "generator_conditions",
    "partitions": null,
    "type": "ALL",
    "possible_keys": "PRIMARY",
    "key": null,
    "key_len": null,
    "ref": null,
    "rows": 4,
    "filtered": 25,
    "Extra": "Using where; Using join buffer (hash join)"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "products",
    "partitions": null,
    "type": "ref",
    "possible_keys": "PRIMARY,products_parent_id_foreign",
    "key": "products_parent_id_foreign",
    "key_len": "9",
    "ref": "hyve_insurance.laravel_reserved_0.id",
    "rows": 4,
    "filtered": 100,
    "Extra": null
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "generator_condition_value_product",
    "partitions": null,
    "type": "ref",
    "possible_keys": "PRIMARY,gcv_id_primary,p_id_primary",
    "key": "p_id_primary",
    "key_len": "8",
    "ref": "hyve_insurance.products.id",
    "rows": 4,
    "filtered": 100,
    "Extra": "Using index"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "generator_condition_values",
    "partitions": null,
    "type": "eq_ref",
    "possible_keys": "PRIMARY,condition_value_unique",
    "key": "PRIMARY",
    "key_len": "8",
    "ref": "hyve_insurance.generator_condition_value_product.generator_condition_value_id",
    "rows": 1,
    "filtered": 10,
    "Extra": "Using where"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "generator_condition_value_product",
    "partitions": null,
    "type": "ref",
    "possible_keys": "PRIMARY,gcv_id_primary,p_id_primary",
    "key": "p_id_primary",
    "key_len": "8",
    "ref": "hyve_insurance.products.id",
    "rows": 4,
    "filtered": 100,
    "Extra": "Using index"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "generator_conditions",
    "partitions": null,
    "type": "eq_ref",
    "possible_keys": "PRIMARY",
    "key": "PRIMARY",
    "key_len": "8",
    "ref": "hyve_insurance.generator_condition_values.generator_condition_id",
    "rows": 1,
    "filtered": 25,
    "Extra": "Using where"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "generator_condition_values",
    "partitions": null,
    "type": "eq_ref",
    "possible_keys": "PRIMARY,condition_value_unique",
    "key": "PRIMARY",
    "key_len": "8",
    "ref": "hyve_insurance.generator_condition_value_product.generator_condition_value_id",
    "rows": 1,
    "filtered": 10,
    "Extra": "Using where"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "generator_conditions",
    "partitions": null,
    "type": "eq_ref",
    "possible_keys": "PRIMARY",
    "key": "PRIMARY",
    "key_len": "8",
    "ref": "hyve_insurance.generator_condition_values.generator_condition_id",
    "rows": 1,
    "filtered": 25,
    "Extra": "Using where"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "generator_condition_value_product",
    "partitions": null,
    "type": "ref",
    "possible_keys": "PRIMARY,gcv_id_primary,p_id_primary",
    "key": "p_id_primary",
    "key_len": "8",
    "ref": "hyve_insurance.products.id",
    "rows": 4,
    "filtered": 100,
    "Extra": "Using index"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "generator_condition_values",
    "partitions": null,
    "type": "eq_ref",
    "possible_keys": "PRIMARY,condition_value_unique",
    "key": "PRIMARY",
    "key_len": "8",
    "ref": "hyve_insurance.generator_condition_value_product.generator_condition_value_id",
    "rows": 1,
    "filtered": 13.33,
    "Extra": "Using where"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "generator_condition_value_product",
    "partitions": null,
    "type": "ref",
    "possible_keys": "PRIMARY,gcv_id_primary,p_id_primary",
    "key": "p_id_primary",
    "key_len": "8",
    "ref": "hyve_insurance.products.id",
    "rows": 4,
    "filtered": 100,
    "Extra": "Using index"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "generator_conditions",
    "partitions": null,
    "type": "eq_ref",
    "possible_keys": "PRIMARY",
    "key": "PRIMARY",
    "key_len": "8",
    "ref": "hyve_insurance.generator_condition_values.generator_condition_id",
    "rows": 1,
    "filtered": 25,
    "Extra": "Using where"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "generator_condition_values",
    "partitions": null,
    "type": "eq_ref",
    "possible_keys": "PRIMARY,condition_value_unique",
    "key": "PRIMARY",
    "key_len": "8",
    "ref": "hyve_insurance.generator_condition_value_product.generator_condition_value_id",
    "rows": 1,
    "filtered": 5,
    "Extra": "Using where; End temporary"
  }
]