MySQL - Simple query sometimes suffers from infinite loop and increasing number of rows_fetched

Question

I've been trying for the past 4 weeks to figure out why queries are executing intermittently forever on Docker Mysql Percona Distribution (percona:8.0.32-24, empty my.cnf). This postscript query is run after importing multiple CSVs generated using MySQL Shell's data mining algorithm. Half of the time, it executes successfully in 2-3 seconds.

Otherwise, even if the correct number of rows_inserted is displayed, it will stop and enter an infinite loop (2+ days) and keep increasing the number of rows_fetched (fig1.png). Why does this query take so long to run and what is it reading the table for endlessly (high rows_fetched)?

CREATE TABLE algo_rca_rule_metric (
 key varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
 rule_id int unsigned NOT NULL,
 context_id int unsigned NOT NULL,
 value double NOT NULL,
 PRIMARY KEY (key,value,rule_id),
 KEY key_rule_id (rule_id,key,value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

** Insert 2 metrics (confidence and support) before the query is run:

INSERT INTO algo_rca_rule_metric
SELECT
   'confidence_order' AS 'key',
   metric_confidence.rule_id,
   metric_confidence.context_id,
   row_number() over (ORDER BY metric_confidence.value ASC, metric_support.value ASC) AS 'value'
FROM algo_rca_rule_metric metric_confidence
LEFT JOIN algo_rca_rule_metric metric_support ON (metric_confidence.rule_id = metric_support.rule_id AND metric_support.key = 'rule_support')
WHERE metric_confidence.key = 'confidence';

The same behavior is observed without INSERT.

See the explanation statement (fig3.png). When an infinite loop occurs, the following is observed:

• SHOW PROCESS LIST : The query is marked status="executing" .

• Display engine innodb status: Query not found in transaction section.

• select * from sys.schema_table_statistics WHERE table_schema = 'DB_NAME' The rows_fetched output seems to grow infinitely. (see fig1.png does not work, fig4.png works, both performed on the same data).

Any help or insight could save a life.

Answer 1

Put the value at the end of the PK:

PRIMARY KEY key_rule_id (rule_id, key, value),
KEY (key, value, rule_id)

Please note that 322 comes from 2 4*80, which means it only uses one column. (The same goes for const.)

• 2 byte length field
• Maximum 4 bytes per utf8mb4 character
• Declaration contains 80 characters

This does not mean that the full 322 bytes are allocated, but this is the "worst case scenario".