Wordpress MySQL wp_options queries

wp.sql

##-- Show WP autoload options length on wp_options table
SELECT option_name, LENGTH(option_value), autoload FROM wp_options ORDER BY autoload DESC, option_name ASC;

##-- Display LENGTH of option_value in wp_options table
SELECT option_name, LENGTH(option_value), autoload FROM wp_options ORDER BY option_value DESC, option_name ASC;

##-- Autoloaded data size, how many entries are in the table, and the first 10 entries by size
SELECT 'autoloaded data in KiB' as name, ROUND(SUM(LENGTH(option_value))/ 1024) as value FROM wp_options WHERE autoload='yes'
UNION SELECT 'autoloaded data count', count(*) FROM wp_options WHERE autoload='yes'
UNION (SELECT option_name, length(option_value) FROM wp_options WHERE autoload='yes' ORDER BY length(option_value) DESC LIMIT 10)

##-- Sort the top items with 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;

##-- Display COUNT of option_value in wp_options grouped by option_name
SELECT option_name, COUNT(*) optioncount FROM wp_options GROUP BY option_name HAVING optioncount > 1 ORDER BY optioncount DESC;
                                                   
##-- Find out if any index exists
SHOW INDEX FROM wp_options;
                                                   
##-- See WP Options Autoload in Bytes
SELECT SUM(LENGTH(option_value)) as autoload_size FROM wp_options WHERE autoload='yes';

##-- Listing any wp_options MySQL indices
SHOW INDEX FROM wp_options WHERE column_name != 'option_id' AND column_name != 'option_name';

##-- Find out if it is a good idea to add an index to your wp_options table by comparing the number of autoload = yes rows to the number of autoload = no rows
##-- Autoload Yes
SELECT COUNT(CASE WHEN autoload = 'yes' THEN 1 END) FROM wp_options;
##-- Autoload No
SELECT COUNT(CASE WHEN autoload = 'no' THEN 1 END) FROM wp_options;

##-- Generally you only want to make an index if the number of autoload = no options greatly outweigh the autoload = yes options.

##-- Create Autoload Index
CREATE INDEX autoloadindex ON wp_options(autoload);

##-- Delete the index
DROP INDEX autoloadindex ON wp_options
                  
##-- Find Duplicate Options Id
SELECT * FROM wp_options WHERE option_id NOT IN (SELECT * FROM (SELECT MIN(n.option_id) FROM wp_options n GROUP BY n.option_name) x)