Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.20
-
None
-
None
Description
A prepared statement which has a good plan (using an index to perform a group by) always works fine at the first execution, but loses the optimization after some more executions, sometimes even at second execution (although this varies).
The plan is always correct if the statement is run directly, instead of via prepare/execute.
Please see below.
|
|
mariadb> prepare test from "explain SELECT MAX(id) AS id FROM balances GROUP BY chain_asset_id, balance_type, address_id";
|
Query OK, 0 rows affected (0.000 sec)
|
Statement prepared
|
|
|
mariadb> execute test;
|
+------+-------------+----------+-------+---------------+--------------------------------------------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+-------+---------------+--------------------------------------------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | balances | range | NULL | idx_balance_type_address_id_chain_asset_id | 465 | NULL | 8821 | Using index for group-by |
|
+------+-------------+----------+-------+---------------+--------------------------------------------+---------+------+------+--------------------------+
|
1 row in set (0.000 sec)
|
|
|
mariadb> execute test;
|
+------+-------------+----------+-------+---------------+--------------------------------------------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+-------+---------------+--------------------------------------------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | balances | range | NULL | idx_balance_type_address_id_chain_asset_id | 465 | NULL | 8821 | Using index for group-by |
|
+------+-------------+----------+-------+---------------+--------------------------------------------+---------+------+------+--------------------------+
|
1 row in set (0.000 sec)
|
|
|
[ ... a dozen tries later ... ]
|
|
|
mariadb> execute test;
|
+------+-------------+----------+-------+---------------+--------------------------------------------+---------+------+---------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+-------+---------------+--------------------------------------------+---------+------+---------+-------+
|
| 1 | SIMPLE | balances | index | NULL | idx_balance_type_address_id_chain_asset_id | 465 | NULL | 5504077 | |
|
+------+-------------+----------+-------+---------------+--------------------------------------------+---------+------+---------+-------+
|
1 row in set (0.000 sec)
|
|
|
mariadb> execute test;
|
+------+-------------+----------+-------+---------------+--------------------------------------------+---------+------+---------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+-------+---------------+--------------------------------------------+---------+------+---------+-------+
|
| 1 | SIMPLE | balances | index | NULL | idx_balance_type_address_id_chain_asset_id | 465 | NULL | 5504077 | |
|
+------+-------------+----------+-------+---------------+--------------------------------------------+---------+------+---------+-------+
|
1 row in set (0.000 sec)
|
|
|
mariadb> execute test;
|
+------+-------------+----------+-------+---------------+--------------------------------------------+---------+------+---------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+-------+---------------+--------------------------------------------+---------+------+---------+-------+
|
| 1 | SIMPLE | balances | index | NULL | idx_balance_type_address_id_chain_asset_id | 465 | NULL | 5504077 | |
|
+------+-------------+----------+-------+---------------+--------------------------------------------+---------+------+---------+-------+
|
1 row in set (0.000 sec)
|
|
Additional notes:
- re-preparing the statement will make it work again (for a while...)
- running an explain of the same query without re-preparing will make it work again (for a while still)
Attachments
Issue Links
- is blocked by
-
-
- Confirmed
-