SQL cheatbook for big WP tables

query.sql

--See what is eating up (duplicate no):
SELECT COUNT(meta_key), meta_key FROM wp_postmeta GROUP BY meta_key ORDER BY COUNT(meta_key) DESC;

-- See what is eating up (size kb/gb):
SELECT meta_key, (SUM(LENGTH(meta_id)+LENGTH(post_id)+LENGTH(meta_key)+LENGTH(meta_value)))/1048576 AS `Size`, COUNT(*) AS `Count` FROM wp_postmeta
 GROUP BY `meta_key`
 ORDER BY `Size` DESC

--Get a key that is duplicate
SELECT * FROM wp_postmeta WHERE `meta_key` LIKE ‘%keyword%’;

--Bulk close orders
UPDATE `wp_posts` SET `post_status` = 'wc-completed' WHERE `post_type` = 'shop_order' AND `post_status` ='wc-processing' AND `post_date` < '2022-01-01 00:00:00';