Fishhawk
Search
Pricing

Checking Autoload Size in phpMyAdmin

-
Grafxion Team Grafxion Team
DatabaseSQL
2025-04-03
To check the overall size of autoloaded data in the wp_options table of your database, log into phpMyAdmin and click on the database name in the left-hand sidebar

Then, under the SQL tab, enter the following query and click the Go button at the bottom-right. Be sure to adjust the wp_ prefix if you use a different table prefix in your database.

SELECT SUM(LENGTH(option_value)) as autoload_size FROM wp_options WHERE autoload='yes';

Ideally, autoloaded data should be under 1MB. If it falls between 3MB and 5MB, there may be opportunities for optimization. However, if it reaches 10MB or more, this signals significant performance issues that require immediate attention.

Sort Top Autoloaded Data

SELECT option_name, length(option_value) AS option_value_length FROM wp_options WHERE autoload='yes' ORDER BY option_value_length DESC LIMIT 10;

To automatically clear up _transient_dirsize_cache tables in WordPress

Using SQL Query (phpMyAdmin):
  • Open phpMyAdmin: and navigate to your WordPress database.
  • Click on the SQL tab .
  • Paste and execute the following query: 
Code
    DELETE FROM `wp_options` WHERE `autoload` = 'yes' AND `option_name` LIKE '%transient%'
  • Note: Replace wp_ with your database table prefix if it’s different. 
  • Alternatively, to delete specific transient keys:
Code
    DELETE FROM `wp_options` WHERE `option_name` = '_transient_dirsize_cache'
  • Note: Replace wp_ with your database table prefix if it’s different. 
Important Considerations:
  • Backup your database: before making any changes.
  • Be cautious when deleting transients, as they store temporary data that might be used by your site.
  • Transients are not always automatically loaded, so clearing them might not always be necessary.
  • Consider using an object cache: for better performance and transient management.
 

To identify unused tables in MySQL

You can enable the general log, analyze it for table access, or use the information_schema.tables table to check update_time. [1, 2, 3]
Here’s a more detailed breakdown:
1. Using the General Log: [2, 3]
  • Enable the General Log: Set the general_log variable to ON: [2, 3]
    SET GLOBAL general_log = 'ON';
  • Analyze the Log: After enabling the log, examine it for SQL statements that access tables. Look for SELECT, INSERT, UPDATE, and DELETE statements that use a specific table name. [2, 3]
  • Disable the Log: Once you’ve finished analyzing the log, disable it to avoid performance impact: [2, 3]
    SET GLOBAL general_log = 'OFF';
2. Using information_schema.tables: [1, 3]
  • Check update_time: The information_schema.tables table contains information about tables, including the update_time column, which indicates when the table was last modified.
  • Query for Inactive Tables: You can use this information to identify tables that haven’t been updated recently, suggesting they might be unused. [1, 3]
    SELECT table_schema, table_name, update_time AS LastAccessed
    FROM information_schema.tables
    GROUP BY table_schema, table_name
    ORDER BY update_time ASC;
  • Note: A table might be updated without being accessed by a SELECT, INSERT, UPDATE, or DELETE statement. So, this method might not be 100% accurate. [1, 3]
3. Other Considerations: [1]
  • SHOW TABLE STATUS: The SHOW TABLE STATUS command provides information about tables, including their creation time and update time. [1]
  • ls -lt on the mysql datadir: If you have InnoDB file-per-table enabled, you can examine the timestamps of the .ibd files in the MySQL data directory. [1]
  • sys.schema_unused_indexes: You can use the sys.schema_unused_indexes view to identify unused indexes, which might indicate that the table is not being used frequently. [4, 5]
  • sys.sql_expression_dependencies: You can use the sys.sql_expression_dependencies view to identify tables that are not being referenced by other objects [6]
Close
Search

Hit enter to search or ESC to close