The optimization process and recommendations:

1. Avoid Subqueries In From Clause (modified query below): The database cannot properly optimize subqueries in the FROM clause. Therefore, we recommend to extract the subqueries to temporary tables, index them and join to them in the outer query.
2. 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 `es_temp1` ADD INDEX `es_temp1_idx_user_id` (`user_id`);
ALTER TABLE `es_temp2` ADD INDEX `es_temp2_idx_user_id` (`user_id`);
ALTER TABLE `es_temp3` ADD INDEX `es_temp3_idx_user_id` (`user_id`);
ALTER TABLE `es_temp4` ADD INDEX `es_temp4_idx_user_id` (`user_id`);
ALTER TABLE `es_temp5` ADD INDEX `es_temp5_idx_user_id` (`user_id`);

The optimized query:

SELECT
        u.id,
        post.post_count,
        updated.update_count,
        print.print_count,
        share.share_count,
        purchase.purchase_count 
    FROM
        users AS u 
    LEFT JOIN
        es_temp1 post 
            ON u.id = post.user_id 
    LEFT JOIN
        es_temp2 updated 
            ON u.id = updated.user_id 
    LEFT JOIN
        es_temp3 print 
            ON u.id = print.user_id 
    LEFT JOIN
        es_temp4 share 
            ON u.id = share.user_id 
    LEFT JOIN
        es_temp5 purchase 
            ON u.id = purchase.user_id