Попала в руки битриксовая БД.
И там есть такой запрос:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SELECT `sale_internals_order`.`ID` AS `ID`, `sale_internals_order`.`PAYED` AS `PAYED`, `sale_internals_order`.`DATE_PAYED` AS `DATE_PAYED`,
`sale_internals_order`.`CANCELED` AS `CANCELED`, `sale_internals_order`.`DATE_CANCELED` AS `DATE_CANCELED`,
`sale_internals_order`.`STATUS_ID` AS `STATUS_ID`, `sale_internals_order`.`DATE_STATUS` AS `DATE_STATUS`,
`sale_internals_order`.`PRICE_DELIVERY` AS `PRICE_DELIVERY`, `sale_internals_order`.`ALLOW_DELIVERY` AS `ALLOW_DELIVERY`,
`sale_internals_order`.`DATE_ALLOW_DELIVERY` AS `DATE_ALLOW_DELIVERY`, `sale_internals_order`.`PRICE` AS `PRICE`,
`sale_internals_order`.`CURRENCY` AS `CURRENCY`, `sale_internals_order`.`DISCOUNT_VALUE` AS `DISCOUNT_VALUE`,
`sale_internals_order`.`PAY_SYSTEM_ID` AS `PAY_SYSTEM_ID`, `sale_internals_order`.`DELIVERY_ID` AS `DELIVERY_ID`,
`sale_internals_order`.`DATE_INSERT` AS `DATE_INSERT`, `sale_internals_order`.`LID` AS `LID`, `sale_internals_order`.`USER_ID` AS `USER_ID`,
`sale_internals_order_user`.`NAME` AS `USER_NAME`, `sale_internals_order_user`.`LAST_NAME` AS `USER_LAST_NAME`
FROM
`b_sale_order` `sale_internals_order`
INNER JOIN `b_user` `sale_internals_order_user` ON `sale_internals_order`.`USER_ID` = `sale_internals_order_user`.`ID`
ORDER BY
`sale_internals_order`.`DATE_UPDATE` DESC
LIMIT 0, 10
который выполняется на Mysql 5.7.18 меньше секунды, а на MariaDB 10.2 - 6-11 сек.
Тип таблицы `b_sale_order` - innoDB.
explain Mysql:
1.
2.
3.
4.
5.
6.
+----+-------------+---------------------------+------------+--------+----------------------------------+-----------------+---------+--------------------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------------+------------+--------+----------------------------------+-----------------+---------+--------------------------------------+------+----------+-------+
| 1 | SIMPLE | sale_internals_order | NULL | index | IXS_ORDER_USER_ID,IXS_SALE_COUNT | IXS_DATE_UPDATE | 5 | NULL | 10 | 100.00 | NULL |
| 1 | SIMPLE | sale_internals_order_user | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.sale_internals_order.USER_ID | 1 | 100.00 | NULL |
+----+-------------+---------------------------+------------+--------+----------------------------------+-----------------+---------+--------------------------------------+------+----------+-------+
explain MariaDB:
1.
2.
3.
4.
5.
6.
+------+-------------+---------------------------+------+----------------------------------+-------------------+---------+--------------------------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------------------------+------+----------------------------------+-------------------+---------+--------------------------------------+--------+---------------------------------+
| 1 | SIMPLE | sale_internals_order_user | ALL | PRIMARY | NULL | NULL | NULL | 299992 | Using temporary; Using filesort |
| 1 | SIMPLE | sale_internals_order | ref | IXS_ORDER_USER_ID,IXS_SALE_COUNT | IXS_ORDER_USER_ID | 4 | test.sale_internals_order_user.ID | 1 | |
+------+-------------+---------------------------+------+----------------------------------+-------------------+---------+--------------------------------------+--------+---------------------------------+
show index from `b_sale_order` на MariaDB:
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.
+--------------+------------+----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| b_sale_order | 0 | PRIMARY | 1 | ID | A | 883163 | NULL | NULL | | BTREE | | |
| b_sale_order | 0 | IXS_ACCOUNT_NUMBER | 1 | ACCOUNT_NUMBER | A | 883163 | NULL | NULL | YES | BTREE | | |
| b_sale_order | 1 | IXS_ORDER_USER_ID | 1 | USER_ID | A | 883163 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_ORDER_PERSON_TYPE_ID | 1 | PERSON_TYPE_ID | A | 2 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_ORDER_STATUS_ID | 1 | STATUS_ID | A | 14 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_ORDER_REC_ID | 1 | RECURRING_ID | A | 2 | NULL | NULL | YES | BTREE | | |
| b_sale_order | 1 | IX_SOO_AFFILIATE_ID | 1 | AFFILIATE_ID | A | 2 | NULL | NULL | YES | BTREE | | |
| b_sale_order | 1 | IXS_ORDER_UPDATED_1C | 1 | UPDATED_1C | A | 2 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_SALE_COUNT | 1 | USER_ID | A | 883163 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_SALE_COUNT | 2 | LID | A | 883163 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_SALE_COUNT | 3 | PAYED | A | 883163 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_SALE_COUNT | 4 | CANCELED | A | 883163 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_DATE_UPDATE | 1 | DATE_UPDATE | A | 883163 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_XML_ID | 1 | XML_ID | A | 883163 | NULL | NULL | YES | BTREE | | |
| b_sale_order | 1 | IXS_ID_1C | 1 | ID_1C | A | 883163 | NULL | NULL | YES | BTREE | | |
| b_sale_order | 1 | IX_BSO_DATE_ALLOW_DELIVERY | 1 | DATE_ALLOW_DELIVERY | A | 2 | NULL | NULL | YES | BTREE | | |
| b_sale_order | 1 | IX_BSO_ALLOW_DELIVERY | 1 | ALLOW_DELIVERY | A | 2 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IX_BSO_DATE_CANCELED | 1 | DATE_CANCELED | A | 894 | NULL | NULL | YES | BTREE | | |
| b_sale_order | 1 | IX_BSO_CANCELED | 1 | CANCELED | A | 2 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IX_BSO_DATE_PAYED | 1 | DATE_PAYED | A | 883163 | NULL | NULL | YES | BTREE | | |
| b_sale_order | 1 | IX_BSO_DATE_INSERT | 1 | DATE_INSERT | A | 883163 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IX_BSO_DATE_PAY_BEFORE | 1 | DATE_PAY_BEFORE | A | 2 | NULL | NULL | YES | BTREE | | |
+--------------+------------+----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
на Mysql:
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.
+--------------+------------+----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| b_sale_order | 0 | PRIMARY | 1 | ID | A | 873718 | NULL | NULL | | BTREE | | |
| b_sale_order | 0 | IXS_ACCOUNT_NUMBER | 1 | ACCOUNT_NUMBER | A | 873718 | NULL | NULL | YES | BTREE | | |
| b_sale_order | 1 | IXS_ORDER_USER_ID | 1 | USER_ID | A | 273119 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_ORDER_PERSON_TYPE_ID | 1 | PERSON_TYPE_ID | A | 1 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_ORDER_STATUS_ID | 1 | STATUS_ID | A | 8 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_ORDER_REC_ID | 1 | RECURRING_ID | A | 1 | NULL | NULL | YES | BTREE | | |
| b_sale_order | 1 | IX_SOO_AFFILIATE_ID | 1 | AFFILIATE_ID | A | 1 | NULL | NULL | YES | BTREE | | |
| b_sale_order | 1 | IXS_ORDER_UPDATED_1C | 1 | UPDATED_1C | A | 1 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_SALE_COUNT | 1 | USER_ID | A | 246046 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_SALE_COUNT | 2 | LID | A | 252733 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_SALE_COUNT | 3 | PAYED | A | 289108 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_SALE_COUNT | 4 | CANCELED | A | 307629 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_DATE_UPDATE | 1 | DATE_UPDATE | A | 462007 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IXS_XML_ID | 1 | XML_ID | A | 855946 | NULL | NULL | YES | BTREE | | |
| b_sale_order | 1 | IXS_ID_1C | 1 | ID_1C | A | 873718 | NULL | NULL | YES | BTREE | | |
| b_sale_order | 1 | IX_BSO_DATE_ALLOW_DELIVERY | 1 | DATE_ALLOW_DELIVERY | A | 1 | NULL | NULL | YES | BTREE | | |
| b_sale_order | 1 | IX_BSO_ALLOW_DELIVERY | 1 | ALLOW_DELIVERY | A | 1 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IX_BSO_DATE_CANCELED | 1 | DATE_CANCELED | A | 446 | NULL | NULL | YES | BTREE | | |
| b_sale_order | 1 | IX_BSO_CANCELED | 1 | CANCELED | A | 1 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IX_BSO_DATE_PAYED | 1 | DATE_PAYED | A | 873718 | NULL | NULL | YES | BTREE | | |
| b_sale_order | 1 | IX_BSO_DATE_INSERT | 1 | DATE_INSERT | A | 873718 | NULL | NULL | | BTREE | | |
| b_sale_order | 1 | IX_BSO_DATE_PAY_BEFORE | 1 | DATE_PAY_BEFORE | A | 1 | NULL | NULL | YES | BTREE | | |
+--------------+------------+----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Подозреваю, что в MariaDB cardinality ошибочный и из-за этого строится неоптимальный план запроса..
Если указать straight_join, то в MariaDB такой же ПЗ как Mysql...
Попытки
1.
analyze table b_sale_order persistent for all
или скопировать таблицу ни к чему не привели..
Кто знает, подскажите пожалуйста..
