# Database Table Sizes and Optimize Buttons

> Adds Tools > Database Tables listing every table of your site with its row count, data and index size and reclaimable space, sorted by size, with an Optimize button for tables that need it.

- Type: PHP
- Runs: Admin only
- Category: [Performance](https://wpide.com/library/category/performance/)
- Tags: database, tables, optimize
- Author: WPIDE Team
- Installs: 0
- Updated: 2026-10-28
- Page: https://wpide.com/library/perf-database-table-sizes/

## Code

```php
add_action( 'admin_menu', function () {
    add_management_page( 'Database Tables', 'Database Tables', 'manage_options', 'wpide-db-tables', function () {
        global $wpdb;

        $tables = $wpdb->get_results( $wpdb->prepare(
            'SELECT TABLE_NAME AS name, ENGINE AS engine, TABLE_ROWS AS row_count, DATA_LENGTH AS data, INDEX_LENGTH AS idx, DATA_FREE AS free
             FROM information_schema.TABLES WHERE TABLE_SCHEMA = %s AND TABLE_NAME LIKE %s ORDER BY ( DATA_LENGTH + INDEX_LENGTH ) DESC',
            DB_NAME,
            $wpdb->esc_like( $wpdb->prefix ) . '%'
        ) );
        $names = wp_list_pluck( $tables, 'name' );

        if ( isset( $_POST['wpide_optimize'] ) && check_admin_referer( 'wpide_optimize_table' ) && current_user_can( 'manage_options' ) ) {
            $table = sanitize_text_field( wp_unslash( $_POST['wpide_optimize'] ) );
            if ( in_array( $table, $names, true ) ) {
                $wpdb->query( 'OPTIMIZE TABLE `' . esc_sql( $table ) . '`' );
                printf( '<div class="notice notice-success"><p>%s</p></div>', esc_html( 'Optimized ' . $table . '.' ) );
            }
        }

        $total = 0;
        echo '<div class="wrap"><h1>Database Tables</h1><table class="widefat striped"><thead><tr><th>Table</th><th>Engine</th><th>Rows (approx.)</th><th>Data</th><th>Indexes</th><th>Reclaimable</th><th></th></tr></thead><tbody>';
        foreach ( $tables as $t ) {
            $total += $t->data + $t->idx;
            printf(
                '<tr><td><code>%s</code></td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>',
                esc_html( $t->name ),
                esc_html( $t->engine ),
                esc_html( number_format_i18n( (int) $t->row_count ) ),
                esc_html( size_format( (int) $t->data, 1 ) ),
                esc_html( size_format( (int) $t->idx, 1 ) ),
                esc_html( $t->free ? size_format( (int) $t->free, 1 ) : '-' )
            );
            if ( $t->free > MB_IN_BYTES ) {
                echo '<form method="post" style="margin:0">';
                wp_nonce_field( 'wpide_optimize_table' );
                echo '<button class="button button-small" name="wpide_optimize" value="' . esc_attr( $t->name ) . '">Optimize</button></form>';
            }
            echo '</td></tr>';
        }
        printf( '</tbody></table><p><strong>%s</strong></p></div>', esc_html( 'Total size: ' . size_format( $total, 1 ) ) );
    } );
} );
```

## Notes

Optimizing locks the table briefly on MyISAM; on InnoDB it rebuilds the table, so run it outside peak hours.

## How to use it

Install the free WPIDE plugin (https://wordpress.org/plugins/wpide/), find "Database Table Sizes and Optimize Buttons" in its snippet library and add it in one click; or create a new PHP snippet in WPIDE (or any code snippets plugin) and paste the code above. More snippets: https://wpide.com/library/