Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
None
-
None
-
Mariadb 11.0.2
Description
Adding below both explain queries with results.
The selectAll query is not using the index.
EXPLAIN SELECT |
*
|
FROM
|
`orders`
|
WHERE
|
amazon_order_id IN ( |
SELECT |
j.azid
|
FROM |
marketplace_order_data m,
|
JSON_TABLE(
|
m.shipment_order_ids,
|
'$[*]' COLUMNS(azid varchar(20) PATH '$') |
) AS j |
WHERE |
shipment_id IN ('XXX') |
);
|
+------+-------------+------------+-------+---------------+------------+---------+-------+--------+-----------------------------------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+-------+---------------+------------+---------+-------+--------+-----------------------------------------------------------------------------------------------------+
|
| 1 | PRIMARY | m | const | shipmentId | shipmentId | 98 | const | 1 | |
|
| 1 | PRIMARY | orders | ALL | NULL | NULL | NULL | NULL | 599922 | |
|
| 1 | PRIMARY | j | ALL | NULL | NULL | NULL | NULL | 40 | Table function: json_table; Using where; FirstMatch(orders); Using join buffer (flat, BNL join) |
|
+------+-------------+------------+-------+---------------+------------+---------+-------+--------+-----------------------------------------------------------------------------------------------------+
|
EXPLAIN SELECT |
amazon_order_id
|
FROM
|
`orders`
|
WHERE
|
amazon_order_id IN ( |
SELECT |
j.azid
|
FROM |
marketplace_order_data,
|
JSON_TABLE(
|
shipment_order_ids,
|
'$[*]' COLUMNS(azid varchar(20) PATH '$') |
) AS j |
WHERE |
shipment_id IN ('XXX') |
);
|
+------+-------------+----------------------------+-------+---------------+-----------------+---------+-------+--------+-----------------------------------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------------------------+-------+---------------+-----------------+---------+-------+--------+-----------------------------------------------------------------------------------------------------+
|
| 1 | PRIMARY | marketplace_order_data | const | shipmentId | shipmentId | 98 | const | 1 | |
|
| 1 | PRIMARY | orders | index | NULL | amazon_order_id | 188 | NULL | 599930 | Using index |
|
| 1 | PRIMARY | j | ALL | NULL | NULL | NULL | NULL | 40 | Table function: json_table; Using where; FirstMatch(orders); Using join buffer (flat, BNL join) |
|
+------+-------------+----------------------------+-------+---------------+-----------------+---------+-------+--------+-----------------------------------------------------------------------------------------------------+
|