Tag: table

Getting WordPress Database Size via WP-CLI

One WP-CLI command that I’ve found handy is this db-size command. It allows you to output a site’s registered database tables along with the data and index size in any format that WP-CLI natively supports, with multiple sort options:

/**
 * Gets size of database tables for the current site.
 *
 * ## OPTIONS
 *
 * [--raw]
 * : Outputs full size in bytes instead of human readable sizes.
 *
 * [--order_by=<Total><total>]
 * : Allows custom ordering of the data.
 * ---
 * default: Total
 * options:
 *   - Table
 *   - Data Size
 *   - Index Size
 *   - Total
 * ---
 *
 * [--order=<asc><asc>]
 * : Allows custom ordering direction of the data.
 * ---
 * default: asc
 * options:
 *   - asc
 *   - desc
 * ---
 *
 * [--format=<format><format>]
 * : Render output in a particular format.
 * ---
 * default: table
 * options:
 *   - table
 *   - csv
 *   - json
 *   - count
 *   - yaml
 * ---
 *
 * @subcommand db-size
 */
public function db_size( $args, $assoc_args ) {
	global $wpdb;

	$output   = array();
	$db_size  = array();
	$order_by = WP_CLI\Utils\get_flag_value( $assoc_args, 'order_by', 'Total' );
	$order    = WP_CLI\Utils\get_flag_value( $assoc_args, 'order', 'asc' );
	$format   = WP_CLI\Utils\get_flag_value( $assoc_args, 'format', 'table' );
	$raw      = (bool) WP_CLI\Utils\get_flag_value( $assoc_args, 'raw', false );

	// Fetch list of tables from database.
	$tables = array_map(
		function( $val ) {
			return $val[0];
		},
		$wpdb->get_results( 'SHOW TABLES;', ARRAY_N ) // phpcs:ignore WordPress.DB.DirectDatabaseQuery
	);

	// Fetch table information from database.
	$report = array_map(
		function( $table ) use ( $wpdb ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			return $wpdb->get_row( "SHOW TABLE STATUS LIKE '$table'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		},
		$tables
	);

	foreach ( $report as $table ) {
		// Keep a running total of sizes.
		$db_size['data']  += $table->Data_length; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
		$db_size['index'] += $table->Index_length; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase

		// Format output for WP-CLI's format_items function.
		$output[] = array(
			'Table'      => $table->Name, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
			'Data Size'  => $table->Data_length, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
			'Index Size' => $table->Index_length, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
			'Total'      => $table->Data_length + $table->Index_length, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
		);
	}

	// Sort table data.
	self::sort_table_by( $order_by, $output, $order );

	if ( ! $raw ) {
		// Make data human readable.
		foreach ( array_keys( $output ) as $key ) {
			$output[ $key ]['Data Size']  = size_format( $output[ $key ]['Data Size'] );
			$output[ $key ]['Index Size'] = size_format( $output[ $key ]['Index Size'] );
			$output[ $key ]['Total']      = size_format( $output[ $key ]['Total'] );
		}
	}

	// Output data.
	WP_CLI\Utils\format_items( $format, $output, array( 'Table', 'Data Size', 'Index Size', 'Total' ) );

	// Output totals if we're not piping somewhere.
	if ( ! WP_CLI\Utils\isPiped() ) {
		WP_CLI::success(
			sprintf(
				'Total size of the database for %s is %s. Data: %s; Index: %s',
				home_url(),
				WP_CLI::colorize( '%g' . size_format( $db_size['data'] + $db_size['index'] ) . '%n' ),
				WP_CLI::colorize( '%g' . size_format( $db_size['data'] ) . '%n' ),
				WP_CLI::colorize( '%g' . size_format( $db_size['index'] ) . '%n' )
			)
		);
	}
}

/**
 * Sorts a table by a specific field and direction.
 *
 * @param string $field The field to order by.
 * @param array  &$array The table array to sort.
 * @param string $direction The direction to sort. Ascending ('asc') or descending ('desc').
 */
private function sort_table_by( $field, &$array, $direction ) {
	// Taken from https://joshtronic.com/2013/09/23/sorting-associative-array-specific-key/ Thanks!
	usort(
		$array,
		function ( $a, $b ) use ( $field, $direction ) {
			$a = $a[ $field ];
			$b = $b[ $field ];

			if ( $a === $b ) {
				return 0;
			}

			switch ( $direction ) {
				case 'asc':
					return ( $a < $b ) ? -1 : 1;
				case 'desc':
					return ( $a > $b ) ? -1 : 1;
				default:
					return 0;
			}
		}
	);
	return true;
}</format></asc></total>Code language: PHP (php)

Here’s some example output from one of my test sites:

$ wp test db-size
 +----------------------------+-----------+------------+-------+
 | Table                      | Data Size | Index Size | Total |
 +----------------------------+-----------+------------+-------+
 | wp_mlp_relationships       | 16KB      | 0B         | 16KB  |
 | wp_redacted_table          | 16KB      | 0B         | 16KB  |
 | wp_redacted_table          | 16KB      | 0B         | 16KB  |
 | wp_3_links                 | 16KB      | 16KB       | 32KB  |
 | wp_term_relationships      | 16KB      | 16KB       | 32KB  |
 | wp_site                    | 16KB      | 16KB       | 32KB  |
 | wp_registration_log        | 16KB      | 16KB       | 32KB  |
 | wp_mlp_site_relations      | 16KB      | 16KB       | 32KB  |
 | wp_mlp_languages           | 16KB      | 16KB       | 32KB  |
 | wp_mlp_content_relations   | 16KB      | 16KB       | 32KB  |
 | wp_links                   | 16KB      | 16KB       | 32KB  |
 | wp_redacted_table          | 16KB      | 16KB       | 32KB  |
 | wp_3_term_relationships    | 16KB      | 16KB       | 32KB  |
 | wp_blog_versions           | 16KB      | 16KB       | 32KB  |
 | wp_2_links                 | 16KB      | 16KB       | 32KB  |
 | wp_2_term_relationships    | 16KB      | 16KB       | 32KB  |
 | wp_2_term_taxonomy         | 16KB      | 32KB       | 48KB  |
 | wp_2_commentmeta           | 16KB      | 32KB       | 48KB  |
 | wp_2_postmeta              | 16KB      | 32KB       | 48KB  |
 | wp_term_taxonomy           | 16KB      | 32KB       | 48KB  |
 | wp_3_commentmeta           | 16KB      | 32KB       | 48KB  |
 | wp_2_termmeta              | 16KB      | 32KB       | 48KB  |
 | wp_2_terms                 | 16KB      | 32KB       | 48KB  |
 | wp_termmeta                | 16KB      | 32KB       | 48KB  |
 | wp_commentmeta             | 16KB      | 32KB       | 48KB  |
 | wp_blogmeta                | 16KB      | 32KB       | 48KB  |
 | wp_blogs                   | 16KB      | 32KB       | 48KB  |
 | wp_2_a8c_cron_control_jobs | 16KB      | 32KB       | 48KB  |
 | wp_redacted_table          | 16KB      | 32KB       | 48KB  |
 | wp_3_terms                 | 16KB      | 32KB       | 48KB  |
 | wp_3_termmeta              | 16KB      | 32KB       | 48KB  |
 | wp_3_term_taxonomy         | 16KB      | 32KB       | 48KB  |
 | wp_redacted_table          | 16KB      | 32KB       | 48KB  |
 | wp_terms                   | 16KB      | 32KB       | 48KB  |
 | wp_3_postmeta              | 16KB      | 32KB       | 48KB  |
 | wp_usermeta                | 16KB      | 32KB       | 48KB  |
 | wp_sitemeta                | 16KB      | 32KB       | 48KB  |
 | wp_users                   | 16KB      | 48KB       | 64KB  |
 | wp_postmeta                | 16KB      | 48KB       | 64KB  |
 | wp_posts                   | 16KB      | 64KB       | 80KB  |
 | wp_signups                 | 16KB      | 64KB       | 80KB  |
 | wp_2_posts                 | 16KB      | 64KB       | 80KB  |
 | wp_3_posts                 | 16KB      | 64KB       | 80KB  |
 | wp_2_comments              | 16KB      | 80KB       | 96KB  |
 | wp_comments                | 16KB      | 80KB       | 96KB  |
 | wp_3_comments              | 16KB      | 80KB       | 96KB  |
 | wp_2_options               | 80KB      | 32KB       | 112KB |
 | wp_3_options               | 80KB      | 32KB       | 112KB |
 | wp_options                 | 176KB     | 32KB       | 208KB |
 +----------------------------+-----------+------------+-------+
 Success: Total size of the database for https://www.example.com is 2.6MB. Data: 1MB; Index: 1.5MBCode language: JavaScript (javascript)

Enjoy!

January 18, 2021

Quick Tip: Export WordPress SQL output via WP-CLI

If for some reason you can’t run wp db query, but need to export SQL output to a CSV or other file, then have a look at this small WP-CLI command I whipped up that should allow this:

/**
 * Runs a SQL query against the site database.
 *
 * ## OPTIONS
 *
 * 
 * : SQL Query to run.
 *
 * [--format=]
 * : Render output in a particular format.
 * ---
 * default: table
 * options:
 * - table
 * - csv
 * - json
 * - count
 * - yaml
 * ---
 *
 * [--dry-run=]
 * : Performa a dry run
 *
 * @subcommand sql
 */
public function sql( $args, $assoc_args ) {
     global $wpdb;
 
     $sql     = $args[0];
     $format  = WP_CLI\Utils\get_flag_value( $assoc_args, 'format', 'table' );
     $dry_run = WP_CLI\Utils\get_flag_value( $assoc_args, 'dry-run', 'true' );
 
     // Just some precautions.
     if ( preg_match( '/[update|delete|drop|insert|create|alter|rename|truncate|replace]/i', $sql ) ) {
         WP_CLI::error( 'Please do not modify the database with this command.' );
     }
 
     if ( 'false' !== $dry_run ) {
         WP_CLI::log( WP_CLI::colorize( '%gDRY-RUN%n: <code>EXPLAIN</code> of the query is below: https://mariadb.com/kb/en/explain/' ) );
         $sql = 'EXPLAIN ' . $sql;
     }
 
     // Fetch results from database.
     $results = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB
 
     // Output data.
     WP_CLI\Utils\format_items( $format, $results, array_keys( $results[0] ) );
}Code language: PHP (php)

I’d add an example here, but I don’t have any right now that I can share 😐 I’ll try to find one later (don’t hold your breath on me remembering to do that)

November 30, 2020