0

After upgrading from Mariadb 10.2 to 10.4 extremely increased query time.

On 10.2 query time is 0.5s.

On 10.4 query time is 14s. Also query plan has changed too.

10.2:

    -------------------------------+---------------------------------+---------+---------------------------------+------+---------------------------------------------------------------+
| id   | select_type  | table       | type        | possible_keys                                    | key                             | key_len | ref                             | rows | Extra                                                         |
+------+--------------+-------------+-------------+--------------------------------------------------+---------------------------------+---------+---------------------------------+------+---------------------------------------------------------------+
|    1 | PRIMARY      | <subquery2> | ALL         | distinct_key                                     | NULL                            | NULL    | NULL                            | 6938 | Using temporary; Using filesort                               |
|    1 | PRIMARY      | a4          | eq_ref      | PRIMARY,category_id_idx                          | PRIMARY                         | 8       | production.ptpv.product_id,const |    1 | Using index                                                   |
|    1 | PRIMARY      | a           | eq_ref      | PRIMARY                                          | PRIMARY                         | 4       | production.ptpv.product_id       |    1 | Using where                                                   |
|    1 | PRIMARY      | a2          | ref         | PRIMARY,category_id_idx                          | PRIMARY                         | 4       | production.ptpv.product_id       |    1 | Using index                                                   |
|    1 | PRIMARY      | t           | ref         | product_option_value_id_idx,sup_id_idx,is_active | product_option_value_id_idx     | 5       | production.ptpv.product_id       |    6 | Using where                                                   |
|    1 | PRIMARY      | t2          | eq_ref      | PRIMARY,tbl_sup_type_idx                         | PRIMARY                         | 8       | production.t.sup_id              |    1 | Using where                                                   |
|    1 | PRIMARY      | a3          | eq_ref      | PRIMARY                                          | PRIMARY                         | 4       | production.a2.category_id        |    1 |                                                               |
|    2 | MATERIALIZED | ptpv        | index_merge | param_id_idx,param_value_id_idx,product_id_idx   | param_id_idx,param_value_id_idx | 152,606 | NULL                            | 9250 | Using intersect(param_id_idx,param_value_id_idx); Using where |

10.4

    +------+--------------+-------------+-------------+--------------------------------------------------+---------------------------------+---------+---------------------------------+------+---------------------------------------------------------------+
| id   | select_type  | table       | type        | possible_keys                                    | key                             | key_len | ref                             | rows | Extra                                                         |
+------+--------------+-------------+-------------+--------------------------------------------------+---------------------------------+---------+---------------------------------+------+---------------------------------------------------------------+
|    1 | PRIMARY      | <subquery2> | ALL         | distinct_key                                     | NULL                            | NULL    | NULL                            | 8404 | Using temporary; Using filesort                               |
|    1 | PRIMARY      | t           | ref         | product_option_value_id_idx,sup_id_idx,is_active | product_option_value_id_idx     | 5       | production.ptpv.product_id       | 11   | Using where                                                   |
|    1 | PRIMARY      | a4          | eq_ref      | PRIMARY,category_id_idx                          | PRIMARY                         | 8       | production.ptpv.product_id,const | 1    | Using index                                                   |
|    1 | PRIMARY      | a           | eq_ref      | PRIMARY                                          | PRIMARY                         | 4       | production.ptpv.product_id       | 1    | Using where                                                   |
|    1 | PRIMARY      | a2          | ref         | PRIMARY,category_id_idx                          | PRIMARY                         | 4       | production.ptpv.product_id       | 1    | Using index                                                   |
|    1 | PRIMARY      | t2          | ALL         | PRIMARY,tbl_sup_type_idx                         | NULL                            | NULL    | NULL                            | 8978 | Using where; Using join buffer (flat, BNL join)               |
|    1 | PRIMARY      | a3          | eq_ref      | PRIMARY                                          | PRIMARY                         | 4       | production.a2.category_id        | 1    |                                                               |
|    2 | MATERIALIZED | ptpv        | index_merge | param_id_idx,param_value_id_idx,product_id_idx   | param_id_idx,param_value_id_idx | 152,606 | NULL                            | 8404 | Using intersect(param_id_idx,param_value_id_idx); Using where |
+------+--------------+-------------+-------------+--------------------------------------------------+---------------------------------+---------+---------------------------------+------+---------------------------------------------------------------+

Is it possible to return to previous query plan ?

optimizer_switch has default values on both servers.

  • 1
    You MUST show query text! Is it possible to return to previous query plan? Try to renew tables' statistic. If this cannot help try to use STRAIGHT_JOIN and FORCE INDEX. – Akina Oct 3 at 13:20
  • Ehm... I will not change the query anyway, it's generated by ORM. I need to know difference between query optimizer configuration for both versions. Query won't help to investigate the query time problems – Valeriy Gaydar Oct 3 at 15:36
  • In your previous configuration, you probably had block_nested_loop turned off; which seems like has been overridden after upgrade. Try disabling it. Check stackoverflow.com/a/46372518/2469308 – Madhur Bhaiya Oct 3 at 16:19
  • "Diff between optimizers"? That would involve more space than this forum allows. As for the "optimizer configuration" -- Well, that assumes that all the differences are exposed to configuration, which I seriously doubt. – Rick James Oct 12 at 5:28
1

Almost always index merge intersect can be improved on by using a composite index. Adding an index can be done without dealing with the ORM.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.