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?
 
UmeshConnect With a Mentor MySQL 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
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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
 
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:
Did u check this??
0
All Courses

From novice to tech pro — start learning today.