The optimization process and recommendations:

1. Avoid Correlated Subqueries (query line: 21): A correlated subquery is a subquery that contains a reference (column: id) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
2. Avoid Correlated Subqueries (query line: 29): A correlated subquery is a subquery that contains a reference (column: id) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
3. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
4. Avoid Subqueries (query line: 13): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
5. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

ALTER TABLE `campaign` ADD INDEX `campaign_idx_is_deleted_status_id` (`is_deleted`,`status`,`id`);
ALTER TABLE `campaign_category` ADD INDEX `campaign_category_idx_id` (`id`);
ALTER TABLE `reserved_reviews` ADD INDEX `reserved_reviews_idx_camping_id_user_id` (`camping_id`,`user_id`);
ALTER TABLE `user_reviews` ADD INDEX `user_reviews_idx_user_id` (`user_id`);
ALTER TABLE `user_reviews` ADD INDEX `user_reviews_idx_campaign_id` (`campaign_id`);

The optimized 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
                user_reviews.campaign_id 
            FROM
                user_reviews 
            WHERE
                user_reviews.user_id = 4
        ) 
        AND c.amt_req > (
            SELECT
                COUNT(reserved_reviews.id) 
            FROM
                reserved_reviews 
            WHERE
                reserved_reviews.camping_id = c.id 
                AND reserved_reviews.user_id != 4
        ) + (
            SELECT
                COUNT(user_reviews.id) 
            FROM
                user_reviews 
            WHERE
                user_reviews.campaign_id = c.id
        )