xvHXAb6k

1##-- Show WP autoload options length on wp_options table
2SELECT option_name, LENGTH(option_value), autoload FROM wp_options ORDER BY autoload DESC, option_name ASC;
3
4##-- Display LENGTH of option_value in wp_options table
5SELECT option_name, LENGTH(option_value), autoload FROM wp_options ORDER BY option_value DESC, option_name ASC;
6
7##-- Autoloaded data size, how many entries are in the table, and the first 10 entries by size
8SELECT 'autoloaded data in KiB' as name, ROUND(SUM(LENGTH(option_value))/ 1024) as value FROM wp_options WHERE autoload='yes'
9UNION SELECT 'autoloaded data count', count(*) FROM wp_options WHERE autoload='yes'
10UNION (SELECT option_name, length(option_value) FROM wp_options WHERE autoload='yes' ORDER BY length(option_value) DESC LIMIT 10)
11
12##-- Sort the top items with autoloaded data
13SELECT option_name, LENGTH(option_value) AS option_value_length FROM wp_options WHERE autoload='yes' ORDER BY option_value_length DESC LIMIT 10;
14
15##-- Display COUNT of option_value in wp_options grouped by option_name
16SELECT option_name, COUNT(*) optioncount FROM wp_options GROUP BY option_name HAVING optioncount > 1 ORDER BY optioncount DESC;
17                                                   
18##-- Find out if any index exists
19SHOW INDEX FROM wp_options;
20                                                   
21##-- See WP Options Autoload in Bytes
22SELECT SUM(LENGTH(option_value)) as autoload_size FROM wp_options WHERE autoload='yes';
23
24##-- Listing any wp_options MySQL indices
25SHOW INDEX FROM wp_options WHERE column_name != 'option_id' AND column_name != 'option_name';
26
27##-- 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
28##-- Autoload Yes
29SELECT COUNT(CASE WHEN autoload = 'yes' THEN 1 END) FROM wp_options;
30##-- Autoload No
31SELECT COUNT(CASE WHEN autoload = 'no' THEN 1 END) FROM wp_options;
32
33##-- Generally you only want to make an index if the number of autoload = no options greatly outweigh the autoload = yes options.
34
35##-- Create Autoload Index
36CREATE INDEX autoloadindex ON wp_options(autoload);
37
38##-- Delete the index
39DROP INDEX autoloadindex ON wp_options
40                  
41##-- Find Duplicate Options Id
42SELECT * 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)