Skip to content

Instantly share code, notes, and snippets.

@davidegreenwald
Last active January 4, 2018 11:15
Show Gist options
  • Save davidegreenwald/a6cae448ab8adccdeab38875a0ca220a to your computer and use it in GitHub Desktop.
Save davidegreenwald/a6cae448ab8adccdeab38875a0ca220a to your computer and use it in GitHub Desktop.
MySQL statement for WordPress wp_posts table breaking down data size for each post_type
-- This will show you the size of post, revision, attachment and other fields in `wp_posts`
-- this method can be applied to any WordPress table to get a breakdown of clutter and data use
-- this will not get you index size, but WP indexes are typically a small fraction of overall table size
SELECT post_type
COUNT(*) AS NUM, -- adds a column with the number of rows for each key value for comparison
SELECT post_type, COUNT(*) AS NUM,
(SUM(LENGTH(ID) -- add all column data together to group the total row data by post_type
+LENGTH(post_author)
+LENGTH(post_date)
+LENGTH(post_date_gmt)
+LENGTH(post_content)
+LENGTH(post_title)
+LENGTH(post_excerpt)
+LENGTH(post_status)
+LENGTH(comment_status)
+LENGTH(ping_status)
+LENGTH(post_password)
+LENGTH(post_name)
+LENGTH(to_ping)
+LENGTH(pinged)
+LENGTH(post_modified)
+LENGTH(post_modified_gmt)
+LENGTH(post_parent)
+LENGTH(guid)
+LENGTH(menu_order)
+LENGTH(post_type)))/1048567 -- divide total bytes by (1024 * 1024) for MB
AS `Data in MB`
FROM wp_posts -- replace with your prefix here
GROUP BY post_type
ORDER BY `Data in MB` DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment