Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Useful SQL snippets for WordPress DB analysis
# 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;
# 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;
# 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.