MySQL: how to analyze a table within a stored procedure in mysql

Hi, I just want to know how to analyze a table within a stored procedure.

Help is required.

Thanks,
EL
el123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UmeshMySQL Principle Technical Support EngineerCommented:
ARe you looking for something like this???
DELIMITER |
CREATE PROCEDURE execute_immediate(in_sql VARCHAR(4000))
BEGIN
 
  SET @tmp_sql=in_sql;
  PREPARE s1 FROM @tmp_sql;
  EXECUTE s1;
  DEALLOCATE PREPARE s1;
 
END;
 
|
 
call execute_immediate("ANALYZE TABLE test.documents");
 
 
Output
=======
 
=============================================
Table           OP        Mst_Type  Msg_Txt
=============================================
test.documents	analyze	   status	OK
=============================================

Open in new window

0
el123Author Commented:
Thanks ushastry,

but as I'm very very new to MySQL, have no idea what they are asking, putting here the same text, as asked to me:

"Ideally, that stored procedure would query the information_schema and analyze all tables of the KM database.

Is it Doable?"

This is the question ... is it doable?
0
UmeshMySQL Principle Technical Support EngineerCommented:
Yes... its doable.. you have two options to do it..

1. Write a shell script /any other script... use the statement "SHOW TABLES FROM KM" then pass each result (table name ) to the above procedure or  query information_schema , get table names and pass it to my prev stored procedure

2. Write another stored procedure which would fetch all the table names from the KM schema or would query information_schema.TABLE_NAME for the KM schema and pass table names to my first stored procedure...

That's it..
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

UmeshMySQL Principle Technical Support EngineerCommented:
Forget above procedure.. this new procedure will take care of everything you wanted.... just pass the schema(DB NAME) to the procedure and done...
DELIMITER |
CREATE PROCEDURE uOptimizeTames(in_schema varchar(100) )
  READS SQL DATA
BEGIN
 
  DECLARE l_table         varchar(100);
  DECLARE l_done          INT DEFAULT  0;
 
  DECLARE cursor1 cursor  FOR
    SELECT TABLE_NAME
	FROM   information_schema.TABLES
	WHERE  information_schema.TABLES.TABLE_SCHEMA='test';
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;
 
	OPEN cursor1;
	cursor_loop:LOOP
	    FETCH cursor1 INTO l_table;
	    IF l_done=1 THEN
		 LEAVE cursor_loop;
	    END IF;
 
    /*Do something with the row fetched*/
 
	  SET @tmp_sql= CONCAT("ANALYZE TABLE ",in_schema,".",l_table);
	  PREPARE s1 FROM @tmp_sql;
	  EXECUTE s1;
	  DEALLOCATE PREPARE s1;
 
 	END LOOP cursor_loop;
	CLOSE cursor1;
	SET l_done=0;
END;
 
|
 
 
call uOptimizeTames("KM");

Open in new window

0
UmeshMySQL Principle Technical Support EngineerCommented:
Any updates on this?
0
el123Author Commented:
how to run this when I'm calling this proc, it is not doing anythng;

-- code as it is --

DELIMITER |
CREATE PROCEDURE OptimizeTables(in_schema varchar(100) )
  READS SQL DATA
BEGIN
 
  DECLARE l_table         varchar(100);
  DECLARE l_done          INT DEFAULT  0;
 
  DECLARE cursor1 cursor  FOR
    SELECT TABLE_NAME
        FROM   information_schema.TABLES
        WHERE  information_schema.TABLES.TABLE_SCHEMA='test';
 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;
 
        OPEN cursor1;
        cursor_loop:LOOP
            FETCH cursor1 INTO l_table;
            IF l_done=1 THEN
                 LEAVE cursor_loop;
            END IF;
 
    /*Do something with the row fetched*/
 
          SET @tmp_sql= CONCAT("ANALYZE TABLE ",in_schema,".",l_table);
          PREPARE s1 FROM @tmp_sql;
          EXECUTE s1;
          DEALLOCATE PREPARE s1;
 
        END LOOP cursor_loop;
        CLOSE cursor1;
        SET l_done=0;
END;
 
|
 
 
call optimizeTables("ARIS");

--- end here ---

on this call line, it is not doing anything, just giving me the next line, how to execute this and how to validate too, that analyze happened or not ...?

thanks!
0
UmeshMySQL Principle Technical Support EngineerCommented:
My mistake... I didn't change this line.. I forget to remove "test" string from the query.. I have corrected it.. also missing "DELIMITER ;" after the procedure ending..


Here is the steps... what I did...  I supplied "test" as parameter.. you shud send "KM"

E:\MySQLMaster\bin>mysql -u root -p
Enter password: ***
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> DELIMITER |
mysql> CREATE PROCEDURE uOptimizeTames(in_schema varchar(100) )
    ->   READS SQL DATA
    -> BEGIN
    ->
    ->   DECLARE l_table         varchar(100);
    ->   DECLARE l_done          INT DEFAULT  0;
    ->
    ->   DECLARE cursor1 cursor  FOR
    ->     SELECT TABLE_NAME
    ->         FROM   information_schema.TABLES
    ->         WHERE  information_schema.TABLES.TABLE_SCHEMA=in_sch
    ->
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;
    ->
    ->         OPEN cursor1;
    ->         cursor_loop:LOOP
    ->             FETCH cursor1 INTO l_table;
    ->             IF l_done=1 THEN
    ->                  LEAVE cursor_loop;
    ->             END IF;
    ->
    ->     /*Do something with the row fetched*/
    ->
    ->           SET @tmp_sql= CONCAT("ANALYZE TABLE ",in_schema,".
    ->           PREPARE s1 FROM @tmp_sql;
    ->           EXECUTE s1;
    ->           DEALLOCATE PREPARE s1;
    ->
    ->         END LOOP cursor_loop;
    ->         CLOSE cursor1;
    ->         SET l_done=0;
    -> END;
    ->
    -> |
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql> call uOptimizeTames("test");
+----------------+---------+----------+----------------------------
| Table          | Op      | Msg_type | Msg_text
+----------------+---------+----------+----------------------------
| test.documents | analyze | status   | Table is already up to date
+----------------+---------+----------+----------------------------
1 row in set (0.00 sec)

+---------------+---------+----------+-----------------------------
| Table         | Op      | Msg_type | Msg_text
+---------------+---------+----------+-----------------------------
| test.gameslog | analyze | status   | Table is already up to date
+---------------+---------+----------+-----------------------------
1 row in set (0.02 sec)

+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| test.product_type | analyze | status   | OK       |
+-------------------+---------+----------+----------+
1 row in set (0.02 sec)

+---------+---------+----------+-----------------------------+
| Table   | Op      | Msg_type | Msg_text                    |
+---------+---------+----------+-----------------------------+
| test.t1 | analyze | status   | Table is already up to date |
+---------+---------+----------+-----------------------------+
1 row in set (0.05 sec)

+---------+---------+----------+-----------------------------+
| Table   | Op      | Msg_type | Msg_text                    |
+---------+---------+----------+-----------------------------+
| test.t2 | analyze | status   | Table is already up to date |
+---------+---------+----------+-----------------------------+
1 row in set (0.09 sec)

+----------------+---------+----------+----------------------------
| Table          | Op      | Msg_type | Msg_text
+----------------+---------+----------+----------------------------
| test.tablename | analyze | status   | Table is already up to date
+----------------+---------+----------+----------------------------
1 row in set (0.09 sec)

+------------+---------+----------+----------+
| Table      | Op      | Msg_type | Msg_text |
+------------+---------+----------+----------+
| test.test1 | analyze | status   | OK       |
+------------+---------+----------+----------+
1 row in set (0.13 sec)

Query OK, 0 rows affected (0.17 sec)

mysql>




Corrected Stored procedure... and how to call
DELIMITER |
CREATE PROCEDURE uOptimizeTames(in_schema varchar(100) )
  READS SQL DATA
BEGIN
 
  DECLARE l_table         varchar(100);
  DECLARE l_done          INT DEFAULT  0;
 
  DECLARE cursor1 cursor  FOR
    SELECT TABLE_NAME
        FROM   information_schema.TABLES
        WHERE  information_schema.TABLES.TABLE_SCHEMA=in_schema;
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;
 
        OPEN cursor1;
        cursor_loop:LOOP
            FETCH cursor1 INTO l_table;
            IF l_done=1 THEN
                 LEAVE cursor_loop;
            END IF;
 
    /*Do something with the row fetched*/
 
          SET @tmp_sql= CONCAT("ANALYZE TABLE ",in_schema,".",l_table);
          PREPARE s1 FROM @tmp_sql;
          EXECUTE s1;
          DEALLOCATE PREPARE s1;
 
        END LOOP cursor_loop;
        CLOSE cursor1;
        SET l_done=0;
END;
 
|
 
DELIMITER ;
 
call uOptimizeTames("KM");

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UmeshMySQL Principle Technical Support EngineerCommented:
Did u check this??
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.