MySQL Bugs: #40367: SHOW on a broad view is taking too long

Bug #40367	SHOW on a broad view is taking too long
Submitted:	28 Oct 2008 8:55	Modified:	22 Jan 2009 17:14
Reporter:	Andreas Streichardt	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.1.28, 5.1.29, 5.0, 5.1 bzr	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	performance, show, VIEW

Description:
I have created some VERY broad views and encountered serious performance issues when running a SHOW command on it. This applies to SHOW CREATE TABLE as well as to SHOW COLUMNS.

I have created a small testcase which shows this:

http://www.3uu.de/x.sql

Of course this is a VERY special use case but interestingly if i scale the number of tables/columns linearly the time needed to perform the query raises exponentially which indicates that there might be a issue somewhere which might hit others (and not so exotic use cases) as well. I tested with 61*2000 columns and the show command ran for about 40 minutes on my computer.

Select performance is perfectly okay. It is just the show which is really really slow. So are shows on the tables of the view.

Furthermore when performing this query SHOW PROCESSLIST tells me that it is checking permissions. I disabled permissions (compiled with NO_EMBEDDED_PERMISSIONS or so) and the result was that the processlist showed nothing so whichever operation is causing these issues doesn't report a status change to the server and thus the processlist is reporting a bogus status.

How to repeat:
Dump in the sql file.

mysql [thread 1]> SHOW COLUMNS FROM x_1;

mysql [thread 2]> show processlist;
+-----+---------+-----------+----------+---------+------+----------------------+------------------+
| Id  | User    | Host      | db       | Command | Time | State                | Info             |
+-----+---------+-----------+----------+---------+------+----------------------+------------------+
| 307 | gpadmin | localhost | opst_mop | Query   |    2 | checking permissions | desc x_1         |

mysql [thread 2 no permissions]> show processlist;
+-----+---------+-----------+----------+---------+------+----------------------+------------------+
| Id  | User    | Host      | db       | Command | Time | State                | Info             |
+-----+---------+-----------+----------+---------+------+----------------------+------------------+
| 307 | gpadmin | localhost | opst_mop | Query   |    2 |                      | desc x_1         |

[...]
[thread 1]38019 rows in set (2 min 39,42 sec)

Thank you for the report.

Verified as described.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Ok. Thought the "Feedback required" status meant that the assigned developer should have provided some feedback. The bug is still open and i seriously can't provide more feedback :S The "bug" is simply there and should be reproducable on any mysql server :S At least i have seen it on several mysql servers.

Andreas,

sorry: this was for internal communication. Stay status as "Open" as I need an answer from another developer.