The optimization process and recommendations:

1. Avoid Calling Functions With Indexed Columns (query line: 39): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `meta_value` is indexed, the index won’t be used as it’s wrapped with the function `STR_TO_DATE`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
2. Avoid Calling Functions With Indexed Columns (query line: 77): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `meta_value` is indexed, the index won’t be used as it’s wrapped with the function `STR_TO_DATE`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
3. Avoid OR Conditions By Using UNION (modified query below): In mosts cases, filtering using the OR operator cannot be applied using indexes. A more optimized alternative will be to split the query to two parts combined with a UNION clause, while each query holds one part of the original OR condition.
4. Use Numeric Column Types For Numeric Values (query line: 39): Referencing a numeric value (e.g. 2016) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
5. Use Numeric Column Types For Numeric Values (query line: 77): Referencing a numeric value (e.g. 2016) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
6. Use UNION ALL instead of UNION (query line: 46): Always use UNION ALL unless you need to eliminate duplicate records. By using UNION ALL, you'll avoid the expensive distinct operation the database applies when using a UNION clause.

The optimized query:

SELECT
        ship_id 
    FROM
        ((SELECT
            DISTINCT l.meta_value AS ship_id 
        FROM
            wp_posts a 
        JOIN
            wp_postmeta f 
                ON ID = f.post_id 
        JOIN
            wp_postmeta g 
                ON ID = g.post_id 
        JOIN
            wp_postmeta h 
                ON ID = h.post_id 
        JOIN
            wp_postmeta d 
                ON ID = d.post_id 
        JOIN
            wp_postmeta i 
                ON ID = i.post_id 
        JOIN
            wp_postmeta l 
                ON ID = l.post_id 
        WHERE
            post_type = 'product' 
            AND f.meta_key = 'first_start_date' 
            AND g.meta_key = 'last_start_date' 
            AND h.meta_key = 'product_group' 
            AND h.meta_value = 'cruises' 
            AND d.meta_key = 'destinationregion' 
            AND i.meta_key = 'destinationarea' 
            AND 'South America' IN (
                d.meta_value, i.meta_value
            ) 
            AND (
                (
                    STR_TO_DATE(f.meta_value, '%b %e, %Y') BETWEEN CONCAT('2016', '-01-01') AND CONCAT('2016' + 1, '-01-01')
                )
            ) 
            AND l.meta_key = 'cruiseship' 
        ORDER BY
            l.meta_value ASC) 
    UNION
    DISTINCT (SELECT
        DISTINCT l.meta_value AS ship_id 
    FROM
        wp_posts a 
    JOIN
        wp_postmeta f 
            ON ID = f.post_id 
    JOIN
        wp_postmeta g 
            ON ID = g.post_id 
    JOIN
        wp_postmeta h 
            ON ID = h.post_id 
    JOIN
        wp_postmeta d 
            ON ID = d.post_id 
    JOIN
        wp_postmeta i 
            ON ID = i.post_id 
    JOIN
        wp_postmeta l 
            ON ID = l.post_id 
    WHERE
        post_type = 'product' 
        AND f.meta_key = 'first_start_date' 
        AND g.meta_key = 'last_start_date' 
        AND h.meta_key = 'product_group' 
        AND h.meta_value = 'cruises' 
        AND d.meta_key = 'destinationregion' 
        AND i.meta_key = 'destinationarea' 
        AND 'South America' IN (d.meta_value, i.meta_value) 
        AND ((CONCAT('2016', '01-01') BETWEEN STR_TO_DATE(f.meta_value, '%b %e, %Y') AND STR_TO_DATE(g.meta_value, '%b %e, %Y'))) 
        AND l.meta_key = 'cruiseship' 
    ORDER BY
        l.meta_value ASC)
) AS union1 
ORDER BY
union1.ship_id ASC