How to execute this script in every schema?

Question

I have this piece of code that runs 'optimize table' to every table of a schema.

However, I need to run it to every table in every schema. How can I do that without causing problems?

set @a=null,@c=null,@b=concat("show tables where",ifnull(concat(" `Tables_in_",database(),"` like '",@c,"' and"),'')," (@a:=concat_ws(',',@a,`Tables_in_",database(),"`))");
Prepare `bd` from @b;
EXECUTE `bd`;
DEALLOCATE PREPARE `bd`;
set @a:=concat('optimize table ',@a);
PREPARE `sql` FROM @a;
EXECUTE `sql`;
DEALLOCATE PREPARE `sql`;
set @a=null,@b=null,@c=null;

I wouldn't recomend do such a thing for all tabels and schemas at once at least not without a backup, which be recommened even when you anly make one of such things. But you can write a stored procedure where 2 loops one for show databases and the other with show tables and then do what ever you want. But i never tried that, so it is only a comment — nbk, Sep 15 '19 at 20:30
you use a loop in the first loop. like here https://stackoverflow.com/a/10298945/5193536 — nbk, Sep 15 '19 at 21:00
Why do you want to do that ? Note that queries like Optimize are time consuming and should really not be run like a bot; because unexpected things can happen and a failure/crash may occur; or if you are in replication, yours slaves will really start to lag bigtime. There are many unexpected ways in which this particular script can cause precious downtime on Production. — Madhur Bhaiya, Sep 21 '19 at 10:05
Arent I supposed to run that often to keep the database optimized? — KenobiBastila, Sep 21 '19 at 10:06
This is a duplicate of [mysql-optimize-all-tables](https://stackoverflow.com/questions/5474662/mysql-optimize-all-tables). Read the comments there and follow the links. You will see, that it's not recommended (at least for InnoDB). — Paul Spiegel, Sep 21 '19 at 15:39
I agree with the comments that running optimize creates a risk of downtime, because it locks the table while it works. For a very large table, it could make a table unwritable for _hours_. I manage thousands of MySQL instances at my job, some tables are > 500GB. I would never run optimize across all tables like this. — Bill Karwin, Sep 22 '19 at 14:48

GMB · Accepted Answer · 2019-09-22T14:47:30.557

Here is a stored procedure for your purpose.

It works by first querying INFORMATION_SCHEMA.TABLES to retrieve the list of schemas and table names. Then it goes into a loop and executes the OPTIMIZE TABLE commands sequentially.

Important note: running such a command on all tables is probably not a good idea. Specifically, you don't (or can't) adress MySQL built-in schemas. I added a WHERE clause in the query that excludes the following schemas: information_schema, performance_schema, mysql and sys. I would suggest that you further restrict the WHERE clause to your specific need (having a fixed list of schema would be a good idea).

DELIMITER $$

CREATE PROCEDURE OptimizeAllTables()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tableName CHAR(100);
    DECLARE tableSchema CHAR(100);

    DECLARE tableList CURSOR FOR 
        SELECT table_schema, table_name 
        FROM information_schema.tables 
        WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN tableList;
    tableListLoop: LOOP
        SET done = FALSE;

        FETCH tableList INTO tableSchema, tableName;
        IF done THEN
            LEAVE tableListLoop;
        END IF;

        SET @VarSQL = CONCAT('OPTIMIZE TABLE `', tableSchema, '`.`', tableName, '`');
        -- SELECT @VarSQL;
        PREPARE stmt FROM @VarSQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;
    CLOSE tableList;

END$$

I would also strongly suggest that you run this procedure in debug mode before executing it for real. For this, you can change this block:

    -- SELECT @VarSQL;
    PREPARE stmt FROM @VarSQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

To:

    SELECT @VarSQL;
    -- PREPARE stmt FROM @VarSQL;
    -- EXECUTE stmt;
    -- DEALLOCATE PREPARE stmt;

This will show the commands without actually executing them.

Demo on DB Fiddle (in debug mode):

-- create a few tables for testing
create table table1(id int primary key);
create table table2(id int primary key);
create table table3(id int primary key);

-- call the procedure
call OptimizeAllTables();

Yields:

| @VarSQL                        |
| ------------------------------ |
| OPTIMIZE TABLE `test`.`table1` |

| @VarSQL                        |
| ------------------------------ |
| OPTIMIZE TABLE `test`.`table2` |

| @VarSQL                        |
| ------------------------------ |
| OPTIMIZE TABLE `test`.`table3` |

How to execute this script in every schema?

1 Answers1