Skip to content

Instantly share code, notes, and snippets.

@fearlex
Last active March 11, 2019 20:32
Show Gist options
  • Save fearlex/4965fe01a3bdf26b5cd5ec2ede9752f1 to your computer and use it in GitHub Desktop.
Save fearlex/4965fe01a3bdf26b5cd5ec2ede9752f1 to your computer and use it in GitHub Desktop.
Wordpress MySQL wp_options queries
##-- 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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment