MySQL Bugs: #59595: CHECK TABLES on a VIEW appears to take the time that it does to run the query

Bug #59595	CHECK TABLES on a VIEW appears to take the time that it does to run the query
Submitted:	18 Jan 2011 20:49	Modified:	10 Mar 2015 15:44
Reporter:	Jaimie Sirovich	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.1.50, 5.1.56	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If one has an extremely slow running view that is not normally run without applying other conditions, say:

SELECT * from SLOW_VIEW LIMIT 1;

... then a check tables on that view will take as long as that query takes to execute.  This is a problem for anyone cronning a check tables.  None of the options (quick, fast, etc.) apply to views.

From the documentation:

"As of MySQL 5.0.2, CHECK TABLE can also check views for problems, such as tables that are referenced in the view definition that no longer exist. "

In real life, that might warn that a slow view will execute the query and therefore in the context of MyISAM acquire a lock on all tables referenced, and possibly take 100 days to execute in something awful like O(N^2).

How to repeat:
Create a view for an O(N^2) query and run CHECK TABLES.

Suggested fix:
Update the documentation or apply a LIMIT 0 to the VIEW before running.

Thank you for the problem report. Indeed, looks like CHECK TABLE execute SELECT for the view without any additional limitations:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.56-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create view v1 as select sleep(20) as a from mysql.user;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from v1;
+---+
| a |
+---+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+---+
5 rows in set (1 min 40.00 sec)

mysql> select * from v1 limit 0;
Empty set (0.00 sec)

mysql> check table v1 fast;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.v1 | check | status   | OK       |
+---------+-------+----------+----------+
1 row in set (1 min 40.00 sec)

We can't repeat this on 5.6.