MySQL Bugs: #55475: Poor performance of `INFORMATION

Bug #55475	Poor performance of `INFORMATION_SCHEMA`.`ROUTINES` table
Submitted:	22 Jul 2010 9:33	Modified:	21 Aug 2010 17:01
Reporter:	Giles McArdell	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.1.49	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	information_schema, procedures, routines

Description:
We have over 400 databases, with as many as 50 Procedures in each.

A simple query on `INFORMATION_SCHEMA`.`ROUTINES` to find the procedures in one DB can now take several minutes.

The same query on `mysql`.`proc` takes less than 1 sec.

I suspect there is a full table scan going on as the ROUTINES query seems to take about the same time no matter which DB is requested. Same goes for Functions too.

How to repeat:
You would need a similar setup to the one described above then try:
select `SPECIFIC_NAME` from `INFORMATION_SCHEMA`.`ROUTINES` where `ROUTINE_SCHEMA` = 'mydbname' and ROUTINE_TYPE = 'PROCEDURE' ;
-- takes at least 3 minutes

select `specific_name` from mysql.proc where db = 'mydbname' and `type` = 'PROCEDURE';
-- take <1sec

Suggested fix:
Possibly modify the function that handles the ROUTINES table to make better use the index on the mysql.proc table?

Thank you for the report.

But version 5.1.39 is old. Also there is bug #49501 fix for which can fix your problem too. Please upgrade to current version 5.1.48, try with it and inform us if problem still exists.

Sorry for the delay, had to schedule the update around our workload.

I can report the exact same results on v5.1.49

Thank you for the feedback.

I created similar environment as yours with 400 databases and 50 procedures in each, but can not consistently repeat described behavior: select from INFORMATION_SCHEMA takes 2,5 - 3 sec each time and select from mysql.proc takes 0.01 sec most time, but sometimes takes 16.5 - 17 sec. Also it is known issue what queries on INFORMATION_SCHEMA are slow. Therefore closing report as "Can't repeat"