Get a list of published posts by word count in WordPress database

How to generate a list of your published WordPress posts by word count in SQL (PHPMyAdmin)

So to get started, you will just need to drag-n-drop the How-to Schema block in the Gutenberg editor. The How-to Schema block can be used on pages that contain a How-to in their title and describe steps to achieve certain requirements.

Total Time Needed :

5

Minutes

Required Tools:

– WordPress PHPMyAdmin access

Steps to configure the How-to Schema:

Step 1

Go to PHPMyAdmin and click on your database in the sidebar (it will likely start with wp_) “SQL” tab

Step 2

Click on the “SQL” tab and paste the following SQL query:
SELECT `ID`, `post_date`, `post_type`, `post_status`, `guid`, SUM( LENGTH(`post_content`) - LENGTH(REPLACE(`post_content`, ' ', '')) + 1 ) AS 'Wordcount' FROM `wp_posts` GROUP BY `ID` HAVING `post_type` = 'post' AND `post_status` = 'publish' ORDER BY `Wordcount` DESC LIMIT 0, 10000
You can change the final number to any integer that is large enough to capture all your posts, if you have 5000 posts, you can set it to 5001 (don’t use commas!)

Step 3

Click “Go” and watch the columns for ID, post date, and URL with Worrdcount listed descending in the last column

Step 5

To save the results, scroll to the bottom and click the middle link, titled “Export”

Step 6

Select “CSV” from the “Format:” dropdown item and hit “Go”