Useful SQL snippets for WordPress DB analysis

post-revision-meta.sql

# Lists revision post IDs and their meta data count in descending order
SELECT post_id, count(*) AS count FROM wp_postmeta LEFT JOIN wp_posts ON post_id = ID WHERE post_type = 'revision' GROUP BY post_id ORDER BY count DESC;

# Tells you how many (total figure) meta rows belong to revision posts
SELECT count(*) as total_revision_meta FROM wp_postmeta INNER JOIN wp_posts ON post_id = ID WHERE post_type = 'revision';

# Lists the post_id number of revisions for posts in descending order by number of revisions
SELECT post_parent as post_id, count(*) AS n_revisions FROM wp_posts WHERE post_type = 'revision' GROUP BY post_parent ORDER BY n_revisions DESC;

report-acf-field-value-numbers.sql

# These queries will help you understand what kind of numbers your ACF data is taking up in your DB and where you might
# be able to employ some reduction strategies to prevent redundant data from being stored in your post meta table.
SELECT count(*) FROM wp_postmeta WHERE meta_key = '{FIELD_NAME}';
SELECT count(*) FROM wp_postmeta WHERE meta_key = '_{FIELD_NAME}';
SELECT count(*) FROM wp_postmeta WHERE meta_key = '{FIELD_NAME}' and meta_value = '{DEFAULT_FIELD_VALUE}';
SELECT count(*) FROM wp_postmeta WHERE meta_key = '{FIELD_NAME}' and meta_value = '';
SELECT count(*) FROM wp_postmeta WHERE meta_key = '{FIELD_NAME}' and meta_value = '0';
SELECT count(*) FROM wp_postmeta WHERE meta_key = '{FIELD_NAME}' and meta_value = null;
SELECT meta_value FROM wp_postmeta WHERE meta_key = '{FIELD_NAME}' group by meta_value;

report-post-type-numbers.sql

# lists number of posts in each post type
SELECT post_type, count(*) AS count FROM wp_posts GROUP BY post_type ORDER BY count DESC;