MySQL Bugs: #117267: Table's trigger metadata/code is loaded always (for SELECTs too)

Bug #117267	Table's trigger metadata/code is loaded always (for SELECTs too)
Submitted:	22 Jan 14:11	Modified:	24 Jan 20:46
Reporter:	Eimantas Jatkonis	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:	8.0.40	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Using table in SQL statement opens table and it is added to table_open_cache.
It is same for read-only operations like SELECT and write INSERT/UPDATE/DELETE.

The problem is with read-only SQL(SELECT) and tables with triggers. Opening such a table results in trigger metadata/code addition to "memory/sql/sp_head::main_mem_root". If table is not subject to modification - loading trigger data has a negative affect on speed performance and memory usage.

Attached script with data initialization and statement examples.
No matter how selection is done (direct or stored procedure), every instance of `CustomTable` in SQL statements adds 2Mb to memory usage. 

Is it possible to have separate caches for open tables - one "light weight" for read-only tables handles, the other for write operations? Or any other optimization?

How to repeat:
see file table_open_example.sql
it contains DB init script and SELECT examples.

database init script with SELECT examples

Attachment: table_open_example.sql (application/octet-stream, text), 10.36 KiB.

Thank you for the report. I'm filing this as Feature Request as this is not a bug.

I found same problem in BUG#44625 and BUG#86821.

Marked as fixed in MySQL 9.1 by BUG#44625 / WL#16455.

It would be great to include this fix in current LTS versions: 8.0 and 8.4
(BUG#44625 has contributed patch for 8.0.32, so task looks implementable)