The optimization process and recommendations:

1. Avoid Correlated Subqueries In Select Clause (modified query below): The aggregation function located in a subquery inside the SELECT clause, is executed once for every matched row. Extracting this subquery to a temporary table will improve performance significantly.
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_visitor_id` (`visitor_id`);
ALTER TABLE `hits` ADD INDEX `hits_idx_visitor_id_id` (`visitor_id`,`id`);
ALTER TABLE `hits` ADD INDEX `hits_idx_id` (`id`);
ALTER TABLE `hits` ADD INDEX `hits_idx_visitor_id_id_2` (`visitor_id`,`id`);

The optimized query:

SELECT
        visitors.id,
        visitors.ip,
        visitors.hash,
        (SELECT
            hits.time 
        FROM
            hits 
        WHERE
            hits.visitor_id = visitors.id 
        ORDER BY
            hits.id ASC LIMIT 1) AS first_hit,
        (SELECT
            hits.time 
        FROM
            hits 
        WHERE
            hits.visitor_id = visitors.id 
        ORDER BY
            hits.id DESC LIMIT 1) AS last_hit,
        (SELECT
            hits.host 
        FROM
            hits 
        WHERE
            hits.visitor_id = visitors.id 
        ORDER BY
            hits.id DESC LIMIT 1) AS last_host,
        (SELECT
            hits.location 
        FROM
            hits 
        WHERE
            hits.visitor_id = visitors.id 
        ORDER BY
            hits.id DESC LIMIT 1) AS last_location,
        es_temp1.total_hits,
        (SELECT
            strftime('%s',
            'now') - hits.time 
        FROM
            hits 
        WHERE
            hits.visitor_id = visitors.id 
        ORDER BY
            hits.id DESC LIMIT 1) AS idle_since 
    FROM
        visitors 
    LEFT JOIN
        es_temp1 
            ON es_temp1.visitor_id = visitors.id 
    WHERE
        idle_since < 30 
    ORDER BY
        last_hit DESC