Why MySQL performs so poor on SHOW PROCEDURE STATUS? I've installed MySQL server with many databases that use the InnoDB engine. With lots of procedures in each DB, mysql.proc table contains more than 250,000 rows and it becomes ultimately slow when I need to run a query like this one:
SHOW PROCEDURE STATUS WHERE db LIKE '%SomeDB%';

The above query takes more than 5 seconds to execure and tweaking MySQL configuration parameters such as myisam_sort_buffer_size or key_buffer_size (which where already large enough) made no difference. I've 256GB memory installed on my server and I don't think it is because of any kind of memory shortage, maybe some misconfiguration that I have no Idea about it. Any hint?
The &quot;meta&quot; commands (SHOW...) are generally not designed for performance.  The presumption is that you rarely, if ever, need to run them.  And when you do, it is more of a &quot;one-time&quot; operation.
Please migrate from MyISAM to InnoDB.  (This will probably have no effect on the Question at hand, but is important for many other reasons.)
Before 8.0, the information for many of the SHOW commands was scattered around various files.  For example, each table had a .frm file containing, effectively, the CREATE TABLE information.  &quot;File open&quot; took time, leading to slowness.
8.0 moved all that info into InnoDB tables so, in theory, it should be straightforward to fetch all the data more efficiently.  However, the implementation seems to involve layers of VIEWs and inadequate indexing.
In any case, 250K of anything takes time.  I could have mentioned the leading wildcard (%) on the LIKE because it prevents the use of an index.  However, I am not convinced that there is a relevant index.