MySQL Bugs: #120407: Performance Improvements: RETURNING clause, JOIN regression fix, io

Bug #120407	Performance Improvements: RETURNING clause, JOIN regression fix, io_uring I/O backend
Submitted:	6 May 5:47	Modified:	7 May 8:48
Reporter:	Hugo Wen	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Three performance improvements that would significantly benefit MySQL users:

1. Add Missing RETURNING Syntax

MariaDB supports RETURNING for DELETE queries:

  DELETE FROM t RETURNING f1;

MySQL is missing this syntax, requiring a multi-step workaround:

  START TRANSACTION;
  SELECT ... FOR UPDATE;    -- locks the row
  DELETE FROM t WHERE id = ...;
  COMMIT;

This impacts performance because it requires a separate locking SELECT
before the DELETE -- doubling round-trips and holding locks longer than
necessary.

2. JOIN Regression

Community-reported degradation on simple JOINs, traced to
btr_search_guess_on_hash (Adaptive Hash Index). Bug #111538
(https://bugs.mysql.com/bug.php?id=111538) was closed but the regression
persists per community reports.

Request: Revisit the AHI regression fix.

3. io_uring Support

MariaDB added io_uring in 10.6 (2021). Active community interest:
  - Percona forum: https://forums.percona.com/t/why-no-io-uring-support/38152
  - MySQL bug #107421: https://bugs.mysql.com/bug.php?id=107421

How to repeat:
  -- 1. RETURNING (not supported):
  DELETE FROM orders WHERE status = 'expired' LIMIT 100 RETURNING order_id, customer_id;
  -- Workaround requires START TRANSACTION + SELECT FOR UPDATE + DELETE + COMMIT

  -- 2. JOIN regression:
  -- Compare sysbench JOIN throughput on 8.0.27 vs 8.0.40 with identical data/hardware

  -- 3. io_uring:
  -- Run sysbench oltp_read_write with 64+ threads on NVMe storage
  -- Compare MySQL (libaio) vs MariaDB 10.6+ (io_uring)

Suggested fix:
1. RETURNING: Implement RETURNING clause for DELETE, INSERT, and UPDATE.

2. JOIN/AHI: Revisit Adaptive Hash Index code path changes from 8.0.28.

3. io_uring: Add innodb_use_io_uring = ON|OFF (default OFF).

Thank you for the useful feature request.

I am however closing this as a duplicate.

The RETURNING clause is requested as a feature in bug#117915.

Please also file separate bugs for separate issues:

The JOIN regression issue seems like a bug and not a feature request.

The io_uring request should have its own feature request in the InnoDB category.

Moving it to InnoDB since there is a separate bug report for RETURNING clause and the other issues are related to InnoDB.