Useful Raw SQL Queries for Wordpress

wp-useful-sql-queries.txt

/* 
This is a query to return a post.ID, post.post_title, and the SKU of
products that do not have an attached image.  The vendor, as associated through
wp-all-import into an ACF field, can be adjusted as needed.
*/
USE featherarms;
SET @vendor = 'CSSI' COLLATE utf8mb4_unicode_ci;
SELECT fa_posts.ID, fa_posts.post_title, fa_postmeta1.meta_value as SKU
FROM fa_posts 
LEFT OUTER JOIN fa_postmeta pm ON (fa_posts.ID=pm.post_id AND pm.meta_key = '_thumbnail_id') 
LEFT OUTER JOIN fa_postmeta pm2 ON (fa_posts.ID=pm2.post_id AND pm2.meta_key = 'dealer') 
LEFT JOIN fa_postmeta fa_postmeta1 on fa_postmeta1.post_id = fa_posts.ID and fa_postmeta1.meta_key = '_sku' 
WHERE fa_posts.post_type = 'product' AND (pm.meta_key IS NULL OR pm.meta_value = '') AND pm2.meta_value = @VENDOR;


/* This query returns the top 5 fattest meta_key collections in your wp_postmeta */
SELECT `meta_key`, COUNT(*) FROM wp_postmeta GROUP BY `meta_key` HAVING  COUNT(*) > 1 ORDER BY COUNT(*) DESC LIMIT 5;

/* Recommended Wordpress Database Indexes */
ALTER TABLE `fa_posts` ADD INDEX `fa_posts_idx_post_status_post_type` (`post_status`,`post_type`);

/* Insert Cross Sells */
SET @POSTID = 122387;
SET @METAVALUE = 'a:2:{i:0;i:65633;i:1;i:65630;}';
INSERT INTO `fa_postmeta` (meta_id, post_id, meta_key, meta_value) VALUES(null, @POSTID, '_crosssell_ids', @METAVALUE) ON DUPLICATE KEY UPDATE meta_value = @METAVALUE;