Last active
February 17, 2021 21:07
-
-
Save ksemel/9591884 to your computer and use it in GitHub Desktop.
Get top-ten lists of post sizes from WordPress database
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Find Table Sizes | |
SELECT CONCAT(table_schema, '.', table_name), | |
CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows, | |
CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA, | |
CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx, | |
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size, | |
ROUND(index_length / data_length, 2) idxfrac | |
FROM information_schema.TABLES | |
ORDER BY data_length + index_length DESC | |
LIMIT 10; | |
# Top Ten largest post_content | |
SELECT ID, | |
post_title, | |
post_type, | |
post_status, | |
LENGTH(post_content) | |
FROM wp_posts | |
ORDER BY LENGTH(post_content) DESC | |
LIMIT 10; | |
# Top Ten largest post_meta values | |
SELECT meta_id, | |
post_id, | |
meta_key, | |
CONCAT( LENGTH(meta_value) ) | |
FROM wp_postmeta | |
ORDER BY LENGTH(meta_value) DESC | |
LIMIT 10; | |
# Top Ten largest option values | |
SELECT option_id, | |
option_name, | |
CONCAT( LENGTH(option_value) ), | |
autoload | |
FROM wp_options | |
ORDER BY LENGTH(option_value) DESC | |
LIMIT 10; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment