The optimization process and recommendations:

1. Avoid Subqueries (query line: 9): 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.
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 `docs` ADD INDEX `docs_idx_superceded_name_effective` (`superceded_by`,`name`,`effective_on`);

The optimized query:

SELECT
        docs.`value` 
    FROM
        docs 
    WHERE
        docs.superceded_by IS NULL 
        AND docs.name = 'p1' 
        AND docs.effective_on = (
            SELECT
                max(docs.effective_on) 
            FROM
                docs 
            WHERE
                docs.superceded_by IS NULL 
                AND docs.effective_on <= '2017-01-01' 
                AND docs.name = 'p1'
        )