MySQL query too slow about 25 seconds

Question

I have a MySQL query and it takes about 25 sec. There are not many rows (just about 200) but I don't understand why it takes long time.

Query:

SELECT *
     , c.id c_id
  FROM campaign c 
  JOIN campaign_category cc 
    ON c.campaign_type = cc.id
 WHERE c.is_deleted = 0
   AND c.status = 1
   AND c.id NOT IN (SELECT campaign_id FROM user_reviews WHERE user_id = 4)
   AND c.amt_req > (SELECT COUNT(id) 
                      FROM reserved_reviews 
                     WHERE camping_id = c.id 
                       AND user_id != 4) 
                 + (SELECT COUNT(id) 
                      FROM user_reviews 
                     WHERE campaign_id = c.id)  

Edit: I tried with JOIN like this but i got no result:

SELECT 
  *, `c`.`id` as `c_id`,COUNT(`ur`.`id`) as `total_reviewed`, COUNT(`rr`.`id`) as `total_reserved`
FROM 
`campaign` `c`
JOIN `campaign_category` `cc` ON `c`.`campaign_type`=`cc`.`id`
JOIN `user_reviews` `ur` ON `ur`.`campaign_id`=`c`.`id`
JOIN `reserved_reviews` `rr` ON `rr`.`camping_id`=`c`.`id`
WHERE 
     `c`.`is_deleted` =0
AND 
      `c`.`status` = 1
AND 
       `ur`.`user_id` != 4 
GROUP BY `c`.`id`
HAVING `c`.`amt_req` > COUNT(`ur`.`id`) + COUNT(`rr`.`id`)

Edit: Table structures: First Image - user_reviews Table, Second image campagin Table, Third image: reserved_reviews Table.

https://i.sstatic.net/dbdcz.png

Answer 1

You can improve this query with indexes;

SELECT *, c.id c_id
FROM campaign c JOIN
     campaign_category cc 
     ON c.campaign_type = cc.id
WHERE c.is_deleted = 0 AND
      c.status = 1 AND
      c.id NOT IN (SELECT campaign_id FROM user_reviews WHERE user_id = 4)
      c.amt_req > (SELECT COUNT(*) 
                   FROM reserved_reviews 
                   WHERE campaign_id = c.id AND user_id <> 4)
                  ) +
                  (SELECT COUNT(id) 
                   FROM user_reviews 
                   WHERE campaign_id = c.id
                  ) ;

For the outer query and joins: campaign(status, is_deleted, id, amt_req) and campaign_category(id) (you should have the latter if it is defined as a primary key.

Then: user_reviews(user_id, campaign_id), reserved_reviews(campaign_id, user_id), and user_reviews(campaign_id).