Suggestion for Indexes on your database, Slow query log filling up

[EdgeCaseBerg]

This plugin is filling up my mysql slow query log with the queries it is doing. For example, the highest culprit is this one (ranked by pt-query-digest on 24MB's of slow query logs)

  SELECT
        sp.id,
        sp.name,
        sp.description,
        sp.code_before,
        sp.code_after,
        sp.place_size,
        sp.place_custom_width,
        sp.place_custom_height,
        sp.patch_img,
        sp.patch_link,
        sp.patch_code,
        sp.patch_adserver,
        sp.patch_dfp,
        sp.patch_source,
        sp.trash,
        (SELECT COUNT(*) FROM wp_sam_ads sa WHERE sa.pid = sp.id AND sa.trash IS FALSE) AS ad_count,
        (SELECT COUNT(*) FROM wp_sam_ads sa WHERE 
              sa.pid = sp.id AND sa.trash IS FALSE 
              AND (IF(sa.ad_users = 0, TRUE, IF(sa.ad_users_reg = 1, IF(sa.x_ad_users = 1, NOT FIND_IN_SET("minnie", sa.x_view_users), TRUE) AND IF(sa.ad_users_adv = 1, (sa.adv_nick <> "minnie"), TRUE), FALSE))) 
              AND ((sa.view_type = 1) OR (sa.view_type = 0 AND (sa.view_pages+0 & 64))) 
              AND (sa.ad_cats = 0)  
              AND (sa.ad_authors = 0)         
              AND IF(sa.ad_schedule, CURDATE() BETWEEN sa.ad_start_date AND sa.ad_end_date, TRUE) 
              AND IF(sa.limit_hits, sa.hits_limit > (SELECT IFNULL(COUNT(*), 0) FROM wp_sam_stats ss WHERE ss.id = sa.id AND ss.event_type = 0), TRUE) 
              AND IF(sa.limit_clicks, sa.clicks_limit > (SELECT IFNULL(COUNT(*), 0) FROM wp_sam_stats ss WHERE ss.id = sa.id AND ss.event_type = 1), TRUE) 
              AND (sa.ad_weight > 0)) AS ad_logic_count,
        (SELECT COUNT(*) FROM wp_sam_ads sa WHERE 
              sa.pid = sp.id 
              AND sa.trash IS FALSE 
              AND (IF(sa.ad_users = 0, TRUE, IF(sa.ad_users_reg = 1, IF(sa.x_ad_users = 1, NOT FIND_IN_SET("minnie", sa.x_view_users), TRUE)AND IF(sa.ad_users_adv = 1, (sa.adv_nick <> "minnie"), TRUE), FALSE))) 
              AND ((sa.view_type = 1) OR (sa.view_type = 0 AND (sa.view_pages+0 & 64))) 
              AND (sa.ad_cats = 0)  
              AND (sa.ad_authors = 0)         
              AND IF(sa.ad_schedule, CURDATE() BETWEEN sa.ad_start_date AND sa.ad_end_date, TRUE) 
              AND IF(sa.limit_hits, sa.hits_limit > (
                    SELECT IFNULL(COUNT(*), 0) FROM wp_sam_stats ss WHERE 
                          ss.id = sa.id AND ss.event_type = 0), TRUE) 
              AND IF(sa.limit_clicks, sa.clicks_limit > (
                    SELECT IFNULL(COUNT(*), 0) FROM wp_sam_stats ss WHERE ss.id = sa.id AND ss.event_type = 1), TRUE)  
              AND IF(sa.ad_weight > 0, (sa.ad_weight_hits*10/(sa.ad_weight*100000)) < 1, FALSE)) AS ad_full_count
  FROM wp_sam_places sp
  WHERE sp.id = 1 AND sp.trash IS FALSE

Running an explain on this query results in showing that there are 7 dependent subqueries as well as 1 primary query. Adding Indexes to the pid field, a covering index to the sp.id and trash field, and another index on the event and view types could help I think. But I am not sure.

[minimus]

In progress...

[minimus]

Done!